'프로그래밍/SQL'에 해당되는 글 2건

[postgresql] UPSERT

1. SQL- upsert 만드는 법 
   기존 오라클에서 사용하던 merge 구문효과를 postgresql 에서 사용하기 위해 UPSERT 문을 만들어 사용한다.

   1)

   INSERT INTO spider_count (spider, tally) VALUES ('Googlebot', 1);
   

   2)

   UPDATE spider_count SET tally=tally+1 WHERE date='today' AND spider='Googlebot';
   

   이와 같은 2개의 구문이 있다고 할 때,변환 방법은 다음 처럼 한다. upsert 라는 변수로 가정하고,

    $insert = "INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1";
   
   $upsert = "UPDATE spider_count SET tally=tally+1 WHERE date='today' AND spider='Googlebot'";
   

2. 위 2개의 변수를 아래 모양에 맞춰 넣는다.

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

   WITH upsert AS ( (UPDATE spider_count SET tally=tally+1 WHERE date='today' AND spider='Googlebot') RETURNING *) INSERT INTO spider_count(spider, tally) SELECT 'Goolgebot', 1 WHERE NOT EXISTS( SELECT * FROM upsert)
   

참고

http://www.the-art-of-web.com

[SQL] rownum, limit(top), order by 순서지정

사용자를 구별하는데 기존의 아이디가 변경될 수 있으니 키 값을 userid로 하지 않고 uniquenum 으로 식별하는 구조로 생각했더니, 
변경전(또는 퇴직) 아이디를 가지고 오는 경우가 발생했다. 
그래서 쿼리를 변경하려고 한다.

일단 잠재적인 문제가 발생할 수 있는 사용자가 얼마나 있는지 확인해보면… 
uniquenum 은 같으나 status가 Y(활성화)된 사용자를 한번 찾아보면…

select a.userid, a.uniquenum , count( a.uniquenum  ) from user_tbl a LEFT OUTER join user_tbl b
on 1=1
and a.uniquenum = b.uniquenum
group by a.userid, a.uniquenum, a.status
having count( a.uniquenum  ) > 1
and a.status = 'Y'

예상데로 여러건들이 나왔다.

여러건이 나올 수 있는 결과에서 1개의 레코드만 필요하고, 특정 필드(예에서는 status)의 원하는 순서데로 뽑아야 한다.

하나의 record 를 뽑으려면 rownum 을 쓰지만, 
이미 rownum 데로 건수를 뽑고 그리고나서 orderby 가 먹힌다. 
과거에 TOP 과 비슷한 것이 생각나 찾아보니, limit 이란 것이 있어 아래와 같이 했다.

결과는 원하는데로 나왔지만, 찝찝하다.. 좀 더 정교하게 콘트롤 하고 싶은데, status 의 상태가 불안하다.

-- postgresql
select userid, uniquenum, status from user_tbl
where 1=1
and uniquenum = 'S123456789'
--and rownum=1
order by status desc
limit 1

찾아 보니, order by 에 케이스를 지정할 수 있었다.

link - sql ORDER BY multiple values in specific order?

2가지 방법이 나오는데, 2번째 방법이 눈에 더 잘 들어와서 
해보니, 잘된다 :) 
그러나 postgresql 에서만 되는 듯. 그리고, 지정을 낮은 단계부터 써야하는 조금 이상하다.

select userid, status from user_tbl
where 1=1
and uniquenum = 'S123456789'
and status in ('N', 'R', 'Y')
order by status = 'N', status = 'R',  status = 'Y'
limit 1

그래도 좀 더 지지를 많이 받는 표현도 익혀보면~

select userid, status from user_tbl
where 1=1
and uniquenum = 'S123456789'
and status in ('R', 'N', 'Y')
order by 
    CASE status 
    when 'Y' then 1
    when 'R' then 2
    when 'N' then 3
    else 4
    end, userid
limit 1;

역시나 많이 쓰는 것으로 채택;;