Archive

Posts Tagged ‘sql’

Postgresq Numerare le righe di una select query – SQL count row_number

In postgresql per aggiungere un contatore al risultato di una query di tipo select è possibile utilizzare la funzione row_number(). L’uso di row_number() richiede la clausola over.  Il formato generale per numerare le righe di una tabella visualizzata con una select query è :

SELECT 
 row_number() OVER() as rownum, table.*
FROM table;

es:

select row_number() OVER () as num,  sp500.* 
 FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Se è necessario che le i records siano ordinati si può utilizzare l’espressione  OVER ( :

SELECT row_number() OVER ( ORDER BY field nulls last) as rownum, table.* 
  FROM table 
  ORDER BY field ;

es:

select row_number() OVER ( order by date nulls last ) as num,  sp500.* 
FROM sp500 WHERE date > 01-01-2010 ORDER BY date;

Se si desidera inserire anche una colonna con il numero totale si può utilizzare la funzione count (*):

select row_number() OVER( order by date nulls last ) as num,  count(*) over ()  as total; sp500.* FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Oracle utilizza row_number

Count()

In postgresql per contare i records che soddisfano una condizione data si utilizza count utilizzando la forma Sql standard.  Ad esempio:

SELECT  count(c.itemid) AS "Num." ,  c."Country" AS codec
FROM contactsdownloads  INNER JOIN contacts AS c
ON (itemidcontacts) = c.itemid

GROUP BY codec
HAVING  count (c.itemid) > 10

Formato generale

SELECT count(* )  over () , a.*
    FROM  table a  ;

Nella query seguente  viene effettuata la somma e il count a partire non da una tabella ma da  un’altra query.
Questo consente di ottenere un count su due livelli di ragruppamento.
In pratica si effettua un primo raggruppamento dei records che ritornerà il primo conteggio quindi si elaboreranno questi dati con un secondo livello di raggruppamento. Qui si effetturà un nuovo count e si opererà la  somma dei dati del primo count.

SELECT nameprd AS "Name Product" , sum(conta) AS "files downloaded", count (itemidcontacts) AS "N.Users" 
       FROM (SELECT  nameprd , count (itemid) AS conta, itemidcontacts FROM contactsdownloads 
               WHERE createddate > '01-01-2007'
               GROUP BY nameprd, itemidcontacts
               ORDER BY conta DESC ) AS DL

     GROUP BY  nameprd
     ORDER BY sum(conta) DESC;

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

postgresql : Eseguire una SQL UPDATE query di test

settembre 20, 2012 Lascia un commento

In diverse circostanze risutla utile eseguire una query di UPDATE in modalità test senza che le modifiche vengano salvate e  diventino persistenti.

Per realizzare una query UPDATE di tipo test in postgresql può essere usato il seguente schema:

BEGIN;
UPDATE .........  ;
SELECT  .......   ;
ROLLBACK  ;

Di seguito un esempio pratico

BEGIN;
UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;
SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;
ROLLBACK;

Esempio semplice

BEGIN ;
UPDATE esf SET "Organization" = company ;
SELECT "Organization" FROM esf WHERE "Organization" is NOT NULL ;
ROLLBACK ;

Altro esempio di sql query di test utilizzando query tools di pgadmin3 in postgresql  con le istruzioni di test roolback incluso disabilitare con il comment  —

Query tool pgadmin3

-- BEGIN; 

UPDATE esf SET 
   "itemid" = contacts.itemid 
   FROM contacts 
   WHERE esf.email = contacts.email AND
   esf.email IN 
   ( SELECT esf.email       
       FROM contacts 
       LEFT JOIN esf ON contacts.email::text = esf.email::text
       WHERE contacts.email::text = esf.email::text AND esf.id < 2000
       ORDER BY esf.createddate ) ;  

-- SELECT"itemid" FROM esf WHERE "itemid" < 22000 ; 

-- ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

creare eliminare database e tabelle

crea database:

create database myportal;

cancella database:

drop database myportal;

crea tabella di backup

CREATE TABLE contacts_backup
       AS SELECT * FROM contacts;

CREATE TABLE "contacts" (
      "itemid" integer NOT NULL,
      "lastname" varchar(25),
      "firstname" varchar(25),
      "email" varchar(50),
      Constraint "contacts_pkey" Primary Key ("itemid")

elimina  tabella

drop table contacts_backup

sql delete

delete FROM links_backup WHERE itemid < 97;

Postgresql cancella record duplicati

Esempio di query SQL per cancellare records duplicati in postgresql

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MAX(dtupla.itemid)
 FROM downloads As dtupla
 GROUP BY dtupla.email,  dtupla. filename,  dtupla.date_part("hour", createddate)

Alternativa per la cancellazione di record duplicati limitando la ricerca ai records
con count (*)   > 1

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MIN(dtpl.itemid)
 FROM downloads As dtpl
 GROUP BY dtpl.email, dtpl.filename, date_trunc('minute',dtpl.createddate)
 HAVING count(*) >= 1);
 SELECT 1;

Conta record che hanno alcuni campi chiave duplicati e che pertatno potrebbero essere record duplicati

SELECT *
 FROM ( SELECT email, date_trunc('hour',createddate), filename, count (filename) as totuples
 FROM contactsdownloads
 GROUP BY email, filename , date_trunc('hour',createddate)
 ORDER BY totuples DESC, date_trunc('hour',createddate) ) AS foo
 WHERE totuples > 1

Risorse:

Postgresql 

Plpgsql 

Creare eliminare database e tabelle

Categorie:Uncategorized Tag:, ,

plpgsql Postgres FOR LOOP END LOOP

Esempio di funzione plpgsql che utilizza
cicli  FOR LOOP END LOOP 
e IF FOUND THEN END IF 

CREATE OR REPLACE FUNCTION __getcatalog_ID0(integer)
RETURNS SETOF catalog AS
$BODY$
DECLARE
_mytupla1 record;
_mytupla2 record;
_myIDC int;
BEGIN
SELECT * INTO _mytupla1 FROM catalog 
    WHERE "IDCatalogFather" = $1 ;
IF FOUND THEN
     -- visualizziamo primo livello distina
     -- ed estraiamo secondo livello distinta
     FOR _mytupla1 IN SELECT * FROM catalog
         WHERE ("IDCatalogFather") = $1
     LOOP
         return next _mytupla1;
         -- estraiamo secondo livello
        _myIDC =  _mytupla1."IDCatalog" ;
        SELECT  * INTO _mytupla2
        FROM catalog  WHERE "IDCatalogFather" = _myIDC ;
        IF FOUND THEN
             return next _mytupla2;
        END IF ;
     END LOOP;
 ELSE
    _myIDC = _mytupla."IDCatalog" ;
    FOR _mytupla in select * from catalog
        WHERE ("IDCatalog") = $1 ;
    LOOP
        return next _mytupla;
    END LOOP;
 END IF;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

Risorse:

Plpgsql 

Postgresql 

Creare eliminare database e tabelle

Postgresql reset sequence

Consistenti modifiche in una tabella rendono necessario talvota resettare il valore
della sequence associata.

Reset sequences : examples query

SELECT  SETVAL(‘documents_itemid_seq’,
( SELECT MAX (itemid)
FROM documents))  WHERE ( SELECT MAX(itemid)  FROM documents) > 0;

select setval(‘events_itemid_seq’,  (select max(itemid) from events))  where(select max(itemid) from events) > 0;

Risorse:

Fixing Sequences – PostgreSQL wiki

Risorse:

Postgresql 

Plpgsql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

postgresql : SQL UPDATE query

SQL UPDATE from another table con SELECT from
1) versione

 UPDATE contacts
   SET countrycode =
   ( SELECT SUBSTR( contacts_c."Country" ,1,2)
      FROM contacts_c
          WHERE  contacts.email = contacts_c.email );

2) versione

UPDATE contacts
SET countrycode = users_ltd.countrycode
FROM users_ltd
WHERE contacts.email = users_ltd.email AND internal IS false;

sql UPDATE con WHERE … IN ( SELECT ) e SELECT con INNER JOIN

UPDATE users_ltd
SET internal = false  

WHERE email IN (
select  users_ltd.email
FROM users_ltd
  INNER JOIN contacts
  ON users_ltd.email = contacts.email
  WHERE internal IS NULL
  ORDER BY contacts.email )

sql Update con due subqueries.

UPDATE  sitepage
   SET  numint = 1.1 * (SELECT   SUM(qty)
   FROM  sitepage_backup
   WHERE sitepage."IDpage" = sitepage_backup."IDpage" )
   WHERE "IDModuleCall" IN
     (SELECT "ModuleID"
      FROM   modules
      GROUP BY  "ModueleID"
      HAVING    sum("ModuleID") >=30)

Update completo di tutte le colonne della tabella

UPDATE sitepage AS S
   SET "IDMenuCatalog"=SB."IDMenuCatalog", "Page"=SB."Page", "PageKey"= SB."PageKey", "Menu"=SB."Menu", 
       "Pagetitle"= SB."Pagetitle", "Banner"=SB."Banner", "Pic3c"=SB."Pic3c", "Selezionato"=SB."Selezionato", "HTML"=SB."HTML", 
       "Description"=SB."Description", "PageBody"=SB."PageBody", "SolutionBody"=SB."SolutionBody", "Nota"=SB."Nota", "HtmlBody"=SB."HtmlBody", 
       "CreatedByUser"=SB."CreatedByUser", "CreatedDate"=SB."CreatedDate", "PageDescription"=SB."PageDescription", "PageKeywords"= SB."PageKeywords", 
       "ModuleIDCall"=SB."ModuleIDCall", "ModuleID"= SB."ModuleID"
 FROM sitepage_backup  AS SB
 WHERE S."IDpage" = 63;

sql eseguire una UPDATE query di TEST in Postgresql

schema:

BEGIN;
UPDATE .........  ;
SELECT  .......   ;
ROLLBACK  ;

Esempio pratico

BEGIN;
UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;
SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;
ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

sql update usando la funzione replace
UPDATE links
 SET url= Replace (url, '/en/products/', '/it/prodotti/' )
 -- WHERE url LIKE '/en/products%';    // agiungere questa riga per avere in query tools le righe modificate

Risorse:

Postgresql 

Plpgsql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

..

Categorie:Server Tag:, , ,