쿠...sal: [컴][DB] postgresql query cheat sheet

cheet

date 계산

SELECT t1.date
 FROM myapp_table AS t1
 WHERE t1.date = (date '2015-05-31' - interval '3 month')::date

2015년 5월 31일의 3달전 날짜(2015년 2월 28일) 에 해당하는 녀석을 retrieve

Update with simple caculation

UPDATE mfactors_stockprice SET (market_capital) = (market_capital*1000000);

Update with Select

WITH correct AS (
  SELECT * FROM myapp_table2
)
UPDATE myapp_table1 t1 SET (pred_correct) =
(SELECT pred_up FROM correct AS t2 WHERE t1.rcp_no=t2.rcp_no)
;

참고 : update 에 select 를 사용하는 것은 ver 9.5 이상 지원

"myapp_table1.pred_correct" 의 값을 set 하는데에 "myapp_table2.pred_up" 값을 사용한다. 이 때 pred_up 은 rcp_no 가 같은 record 의 pred_up 값을 이용한다.

myapp_table1 과 rcp_no 가 같은 myapp_table2의 record 의 pred_up 값을 가져온다.
그래서 이 녀석을 myapp_table1 의 pred_correct 에 assign 한다.

Upsert

WITH upsert AS (
    UPDATE myapp_table SET cnt=cnt+1 
    WHERE date='today' AND name='Jenny' RETURNING *
)
INSERT INTO myapp_table (name, cnt) SELECT 'Jenny', 1 
WHERE NOT EXISTS (SELECT * FROM upsert);

WITH upsert AS ($upsert RETURNING *) $insert WHERE NOT EXISTS (SELECT * FROM upsert);

참고 : A basic UPSERT in PostgreSQL < SQL | The Art of Web

update 하고(WITH), update 가 안되면(WHERE NOT EXISTS), insert 를 한다.

Window function 의 ORDER BY 사용

order by 를 이용해서 각 row 별로 다른 결과를 얻을 수 있다.(참고)

SELECT salary, sum(salary) OVER () FROM empsalary;
 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)


SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

특정 column 으로 group 하고, 그중에 가장 큰 값만 보여주는 것

sql - Select first row in each GROUP BY group? - Stack Overflow

code 로 grouping 하고 이중에 date 가 가장 큰(날짜가 가장 최근) 수만 보여주는 query

SELECT DISTINCT ON (code) 
id, code, date
FROM forecast_consensus where target_date = '2016-03-01' 
ORDER BY code, date DESC NULLS LAST LIMIT 10

date table 이 있을 때 모든 달에 특정날짜를 추출

매달 30일을 추출할 때(2월인 경우 그 달의 마지막 날(last day) 를 추출)

SELECT t1.date
 
FROM full_date_table AS t1 
WHERE date_part('day', t1.date) = CASE WHEN date_part('day', date_trunc('month', t1.date) + interval '1 month -  1 day') >= 30 THEN 30
  ELSE date_part('day', date_trunc('month', t1.date) + interval '1 month -  1 day') END
 
LIMIT 50

쿠...sal

[컴][DB] postgresql query cheat sheet