나무정원

web : Apache

/ap_log/ssl : web서버 ssl 로그

was : tomcat

/tc_log/logback : 로그1

/tc_log/tomcat/catalina :  catalina 로그

/tc_log/ap계정명 : ap로그

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

https://programmers.co.kr/learn/courses/30/lessons/77487

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

-- 코드를 입력하세요
select p.id, p.name, p.host_id
from places p
     ,(SELECT host_id
         from places
     group by host_id
     having count(host_id)>1
     order by 1)sub1
where p.host_id=sub1.host_id


id name host_id
4431977 BOUTIQUE STAYS - Somerset Terrace, Pet Friendly 760849
5194998 BOUTIQUE STAYS - Elwood Beaches 3, Pet Friendly 760849
16045624 Urban Jungle in the Heart of Melbourne 30900122
17810814 Stylish Bayside Retreat with a Luscious Garden 760849
22740286 FREE PARKING - The Velvet Lux in Melbourne CBD 30900122

https://programmers.co.kr/learn/courses/30/lessons/62284#qna

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

-- 코드를 입력하세요
SELECT cart_id
from cart_products
where name ='Milk' or
      name='Yogurt'
group by cart_id
having count(distinct(name)) > 1
order by cart_id

cart_id
286
448
578
977
1048

https://programmers.co.kr/learn/courses/30/lessons/59414

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.
시각(시-분-초)을 제외한 날짜(년-월-일)만 보여주세요

SQL문을 실행하면 다음과 같이 나와야 합니다.
ANIMAL_ID NAME 날짜
A349996 Sugar 2018-01-22
A350276 Jewel 2017-08-13
A350375 Meo 2017-03-06
A352555 Harley 2014-08-08
A352713 Gia 2017-04-13

-- 코드를 입력하세요
SELECT animal_id, name, to_char(datetime,'YYYY-MM-DD') as 날짜
from animal_ins
order by 1