나무정원

내가 실행하고 싶었던 쿼리

insert into board (seq, title, writer, content) 
values((select nvl(max(seq), 0)+1 from board),'안녕','안녕','안녕');

최종 수정/실행쿼리

insert into board (seq, title, writer, content) 
select coalesce(max(seq),0)+1 ,'안녕','안녕','안녕' from board;

** 알게된점

#1. mysql에는 nvl함수 없음
 coalesce(expr1,expr2)
  == CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

 또는 isnull함수를 사용

#2. mysql에는 insert 의 values구문안에 select를 사용할 수 없음

최종 쿼리처럼 insert into 테이블명(속성) values(속성값) 가아닌,

insert into 테이블명(속성) select ~~ (속성값) from 테이블명 형식으로 써주어야 함

#3. 게시판 시퀀스번호 자동부여

max(seq)+1
 // 기존 seq의 최댓값 구하고 +1

CTE ( Common Table Expression )을 통해 공통테이블 식을 사용해서 임시로 테이블 정의/재활용 ==> WITH문

with v1 as (

select eid as eid, atvy_type, ingrs as ingrs, count(*) as cnt1, count(distinct(key1)) as cnt2, 

       count(link_type)as cnt3

   from table1

   where 1=1

 group by eid, atvy_type, ingrs, link_type

 )

 select v1.eid, e3.title, atvy_type, v1.ingrs,c.comn_cd_nm, v1.cnt1, cnt2, cnt3

  from v1 inner join table3 as e3

    on v1.eid=e3.eid

   left outer join table2 as c

   on v1.ingrs=c.comn_cd

order by 1,2,3,4;

v1을 생성해서 

v1결과값과 후행 테이블을 join 해서 결과값 출력

result

 c1 = 전체합계(null포함) , c2 = 중복제거 , c3 = 추가조건

https://wiki.postgresql.org/wiki/CTEReadme

--한달이내 마지막 접속일 조회

select b.col1,count(1) 

from table1 a

          left join 

          table2 b

          on a.col1 = b.col1

where  b.last_date between current_date-1 * interval '30 day' and current_date+1* interval '1 day'

 group by b.col1 

 order by col1

   limit 30; 

--특정기간 마지막 접속일 조회

select b.col1,count(1) 

from table1 a

          left join 

          table2 b

          on a.col1 = b.col1

where b.last_date between

'2021-01-01 00:00:00'  and '2021-01-01 00:00:00'

 group by b.col1 

 order by col1

   limit 30; 

-- 월별 데이터 집계

select to_char(reg_dt, 'YYYY-MM') as ym , count(*) from table1

where reg_dt between '2021-01-01' and '2021-06-30'

group by ym

order by ym;

result

-- 일별 데이터 집계

select to_char(reg_dt, 'YYYYMMDD') as ymd , count(*) from table1

where reg_dt between '2021-01-01' and '2021-01-30'

group by ymd

order by ymd;

-- 메일 발송현황  집계 (발송일기준 / 건수 / 발송시작시간 / 발송종료시간)

select substring(send_date,1,10) as ymd , count(*),  min(send_date) as min ,max(send_date) as max

  from table1

where send_date between '2021-06-01 00:00:00' and '2021-07-22 23:59:00'

and col1='MAIL'

group by ymd 

order by ymd;

result

-- 시간대별 건수

select to_char(reg_Date, 'MM/DD HH24') as ymdh, count(1)

from table1

where reg_date between '2021-01-01 00:00:00' and '2021-04-06 23:59:59'

and col1='CODE1'

group by ydmh

order by ymdh asc;

result

to_date(substring(end_date,1,8),'YYYYMMDD')- to_date(substring(start_date,1,8),'YYYYMMDD')

A에는 있고, B에는 없는 대상을 찾는다.

그 결과를(A에만 있는영역)  A에서 삭제하는 쿼리

limit 으로 100,000만건씩 제거한다.

delete 

  from table1

where col1 in

(

     select a.col1

       from table1 a

      left outer join table2 b

       on a.col1 = b.col1

       where b.col1 is null

       limit 100000

);

delete ~ limit 1 은 먹히지 않았다.

select 문으로 한번 감싸줘야 처리됨

12345는 A와 B 둘다 있다.

34567은 A에는 있고, B에는 없다. (==> 위 조건에서 삭제되어야 할 대상)

98765는 B에만 있다. A기준 left join이므로, 위 데이터는 포함되지 않음

--특정조건(2021년 1월이전)  limit으로 분할하여 update

update table1

set col3= null

where col1 in 

 (

    select col1 

       from table1

       where reg_date <'2021-01-01'

         and col3 is not null

        limit 200000

);

-- limit 으로 분할하여 삭제처리

delete 

  from table1

where data in (select data from table1 limit 50000);

-- row_number()로 row별 1번~순서 매겨서 tmp1 테이블에 저장

create table tmp1  as

select (row_number() over()) as rn, col3

from

(

     select distinct(b.col3) as col3

     from table1 a

             left join 

             table2 b

     on a.col1 = b.col2

      where a.col2='Y'

      and b.col3 is not null

);

result 
select * from tmp1;

• input_text – This defines the input text string, which will be decoded.
• type_text – This defines the in which the input text we expect to be decoded.

---- 연령대별 집계후 더하기 ( age10 / age 20 ~ )

select sum(decode(result,'00',1)) as age00,

   sum(decode(result,'10',1)) as age10,

   sum(decode(result,'20',1)) as age20,

   sum(decode(result,'30',1)) as age30,

   sum(decode(result,'40',1)) as age40,

   sum(decode(result,'50',1)) as age50,

   sum(decode(result,'60',1)) as age60,

   sum(decode(result,'70',1)) as age70,

   sum(decode(result,'80',1)) as age80,

   sum(decode(result,'90',1)) as age90,

   sum(decode(result,'100',1)) as age100,

   sum(decode(result,'NULL','1')) as ageNull    

from 

(

select case when (p.age >=1) and  (p.age<10) then '00'

     when (p.age >=10) and (p.age<20) then '10'

     when (p.age >=20) and (p.age<30) then '20'

     when (p.age >=30) and (p.age<40) then '30'

     when (p.age >=40) and (p.age<50) then '40'

     when (p.age >=50) and (p.age<60) then '50'

     when (p.age >=60) and (p.age<70) then '60'

     when (p.age >=70) and (p.age<80) then '70'

     when (p.age >=80) and (p.age<90) then '80'

     when (p.age >=99) and (p.age<100) then '90'

     when (p.age <=100) then '100'

     else 'NULL' end as result

from table1 u

left join

table2 p

on u.c1=p.c1

where u.c3 > 0

);

result

-- 특정 버전별 (OS 버전별 구해서 sum)

select   sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|10', 1)) as iOs10

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|11', 1)) as iOs11

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|12', 1)) as iOs12

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|13', 1)) as iOs13

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|14', 1)) as iOs14

         ,sum(decode(b.os_type||'|'||substring(b.os_version,1,2),'ios|15', 1)) as iOs15

  from table1 a

          left join 

          table2 b

          on a.c1 = b.c1

where -- b.c2_date  >= now() - interval '1 months' 

     a.c3 > 0

   limit 1;

result

--이벤트 (코드별 type1~6,기타로 구분하기)

select a.id, a.title, decode(b.ingrs,'INGRS-MATO','type1','INGRS-MAMI','type2','INGRS-LNB1','type3','INGRS-FAML','type4','INGRS-STOR','type5','INGRS-PUSH','type6','기타') as gubun, a.reg_date, count(1)  

from familybox.table1 a, familybox.table2 b

where a.col1 = 'Y'

and a.col2 >= '2021-01-01 00:00:00'

and a.col3=b.col3

group by a.ed, a.title, b.ingrs, a.reg_date

order by a.reg_date, gubun asc;

result