/****************************** * Set Operations ******************************/ CREATE TEMP TABLE email (firstname text, lastname text, email text); INSERT INTO email(firstname, lastname, email) values('Jordan', 'Lawrence', 'jordanl@gmail.com'), ('Tammy', 'Standish', 'tstandish@msn.com'), ('Lester', 'Roberts', 'lr@yahoo.com'), ('Lynn', 'Kellerman', 'kellerman@gmail.com'); SELECT lastname, firstname, email, 'temptable' as tblSource from email UNION SELECT PersonLastname, personfirstname, personemail, 'Persontable' FROM person JOIN personaddress USING (personkey) WHERE personaddressCity='Bellevue'; SELECT Personlastname lastname, personfirstname firstname, personemail email, 'donor' "role" FROM person JOIN donation USING(personkey) Where donationamount >=2000 UNION SELECT Personlastname lastname, personfirstname firstname, personemail email, 'client' FROM person JOIN grantapplication USING(personkey) WHERE granttypekey=2; SELECT personkey, personlastname, personfirstname FROM person JOIN donation USING(personkey) INTERSECT SELECT personkey, personlastname, personfirstname FROM person JOIN grantapplication USING(personkey); SELECT personaddresscity FROM personaddress JOIN person USING (personkey) JOIN donation USING (personkey) INTERSECT SELECT personaddresscity FROM personaddress JOIN person USING (personkey) JOIN grantapplication USING (personkey); SELECT personaddresscity FROM personaddress JOIN person USING (personkey) JOIN donation USING (personkey) EXCEPT SELECT personaddresscity FROM personaddress JOIN person USING (personkey) JOIN grantapplication USING (personkey); SELECT granttypename FROM granttype EXCEPT SELECT granttypename FROM grantapplication JOIN granttype USING (granttypekey); /***************************** * Windows Functions *****************************/ SELECT granttypename, grantapplicationkey, grantapplicationamount, RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount DESC) FROM grantapplication JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey WHERE granttypename='Food'; SELECT granttypename, grantapplicationkey, grantapplicationamount, DENSE_RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount DESC) FROM grantapplication JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey WHERE granttypename='Food'; SELECT grantapplicationkey, granttypename, grantapplicationamount, ROW_NUMBER() OVER(ORDER BY grantapplicationkey) FROM grantapplication JOIN granttype using(granttypekey); SELECT grantapplicationkey, granttypename, grantapplicationamount, ROW_NUMBER() OVER(ORDER BY grantapplicationamount) FROM grantapplication JOIN granttype using(granttypekey); SELECT * FROM (SELECT grantapplicationkey, granttypename, grantapplicationamount, ROW_NUMBER() OVER(ORDER BY grantapplicationamount) FROM grantapplication JOIN granttype using(granttypekey))grants WHERE ROW_NUMBER BETWEEN 20 and 30; SELECT * FROM (SELECT grantapplicationkey, granttypename, grantapplicationamount, ROW_NUMBER() OVER(ORDER BY grantapplicationamount) FROM grantapplication JOIN granttype using(granttypekey))grants WHERE ROW_NUMBER BETWEEN 20 and 30; SELECT granttypename, grantapplicationamount, LAST_VALUE(grantapplicationamount) OVER (PARTITION BY granttypekey ORDER BY Grantapplicationamount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM grantapplication JOIN granttype using(granttypekey); /************************************** * Pivot table with CROSSTAB *************************************/ CREATE TEMP TABLE applications2018 ( GranttypeName TEXT, applicationdate DATE, applciationamount NUMERIC ); INSERT INTO applications2018 SELECT Granttypename, grantapplicationDate, grantapplicationamount FROM grantapplication JOIN granttype ON granttype.granttypekey = grantapplication.granttypekey WHERE EXTRACT (YEAR FROM grantapplicationdate)=2018; SELECT * FROM CROSSTAB('SELECT EXTRACT(MONTH FROM applicationdate)::INTEGER, granttypename, SUM(applciationamount) FROM applications2018 GROUP BY 1,2 ORDER BY 1, 2') FINAL_RESULT(Month INTEGER, Food NUMERIC, Rent NUMERIC, School NUMERIC, Dental NUMERIC, Medical NUMERIC, Childcare NUMERIC ,Misc NUMERIC ); CREATE TEMP TABLE citydonations ( "Month" Integer, city text, amount numeric ); CREATE TEMP TABLE citydonations ( "Month" Integer, city text, amount numeric ); INSERT INTO citydonations SELECT EXTRACT(MONTH FROM Donationdate), PersonaddressCity, donationamount FROM ( SELECT DINSTINCT ON(donationkey) donationkey, donationdate, personaddresscity, donationamount FROM donation JOIN personaddress USING (personkey) ) donations; SELECT * FROM CROSSTAB('SELECT "Month" :: INTEGER, City, SUM(amount) FROM citydonations GROUP BY 1,2 ORDER BY 1, 2') FINAL_RESULT(Month INTEGER, Seattle NUMERIC, Redmond NUMERIC, "New York" NUMERIC, Bellevue NUMERIC, Tukwilla NUMERIC, Kent NUMERIC);
Sunday, July 7, 2019
Advanced Query code
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment