[PostgreSQL] Decode

decode(string input_text, format type_text)

• 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

age00	age10	age20	age30	age40	age50	age60	age70	age80	age90	age100	agenull
3	66	100	88	40	44	25	17	2	null	0	50

 

-- 특정 버전별 (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

ios10	ios11	ios12	ios13	ios14	ios15
3	66	100	88	40	44

 

--이벤트 (코드별 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

id	title	gubun	reg_date	count
12345	제목1	type1	2021-12-27 14:40:00	188,900
12345	제목1	type2	2021-12-27 14:41:00	50,000
12345	제목1	type3	2021-12-27 14:42:00	42,000