exhibitlove

출처: https://ga-you-ni.tistory.com/m/238 [postgreSQL] [그룹함수] GROUPING SETS, ROLLUP, CUBE--8)GROUPING SET절 --여러개의 UNION ALL을 이용한 SQL과 같은 결과를 도출할 수 있다. select C1, C2, 집계함수(C3) from TABLE_NAME group by grouping sets (--grouping set절을 이용하면 한번에 다양한 기준의 컬럼 조합으ga-you-ni.tistory.comselect case when grouping(BRAND) = 0 and grouping(SEGMENT) = 0 then '브랜드별+등급별' when grouping(BRAND) = 0 and grouping(SEGMENT..

-- codeCommon.selectCodeTableList : 코드 테이블 목록 SELECT COUNT(*) OVER() cnt , CONCAT(n.nspname,'.', c.relname) AS tbname , OBJ_DESCRIPTION(c.OID) AS tbkrname FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.OID WHERE c.relkind = 'r' AND nspname IN ( 'sppdb', 'spsdb' ) -- codeCommon.selectTableLayout : 코드 테이블 Layout SELECT PS.SCHEMANAME AS SCHEMA , PS.RELNAME , P..

SELECT GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 day');

출처 : /* 회원 통계 */ SELECT COUNT(*) 신규회원가입수 , COUNT(1) FILTER (WHERE customer_type_code ='100') AS naver회원가입 , COUNT(1) FILTER (WHERE customer_type_code ='200') AS google회원가입 , COUNT(1) FILTER (WHERE customer_type_code ='300') AS facebook회원가입 FROM ming.customer_table WHERE 1=1 AND CREATE_DATETIME > CURRENT_DATE - INTERVAL '7 DAY' --지난 일주일간

출처 : https://brownbears.tistory.com/537 SELECT C1, C2, C3, 집계함수(C4) FROM TABLE_NAME GROUP BY ROLLUP (C1, C2, C3); -- 특정 컬럼 제외 가능 SELECT C1, C2, C3, 집계함수(C4) FROM TABLE_NAME GROUP BY C1 ROLLUP (C2, C3);

SELECT Count(*) FILTER(WHERE pay_ymd between '2023-10-01' and '2023-10-31') as pay_cnt FROM tb_pay_tx_dtl

WITH MERGE_ITVL_TYPE AS ( UPDATE SPPDB.TB_ITVL_TYP SET ITVL_TYP_CD_NM = #itvlTypeCodeName) ,ITVL_TYP_CD_EXPL = #{itvlTypeCodeExpl} ,DPST_USE_YN = #{itvlDpstuseFlag} ,ADJ_USE_YN =#{itvlAdjuseFlag} ,USE_YN= #{itvlUseFlag} ,UPDT_DT = NOW() WHERE ITVL_TYP_CD = #{itvlTypeCode} AND ITVL_TRM = #{itvlTerm} RETURNING * ) INSERT INTO SPPDB.TB_ITVL_TYP (ITVL_TYP_CD, ITVL_TRM, ITVL_TYP_CD_NM, ITVL_TYP_CD_EX..

출처:https://blog.nachal.com/m/1631 1. 테이블의 주석(코멘트/설명) 출력 /* 테이블 주석 셀렉트 */ SELECT N.NSPNAME, C.RELNAME, OBJ_DESCRIPTION(C.OID) FROM PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID WHERE C.RELKIND = 'r' AND NSPNAME = '데이터베이스명' AND RELNAME = '테이블명' 2. 테이블의 컬럼명과 주석(코멘트/설명) 출력 /* 컬럼 주석 셀렉트 */ SELECT PS.RELNAME AS TABLE_NAME, PA.ATTNAME AS COLUMN_NAME, PD.DESCRIPTION ..