提取近一年内每个月交易量均超过100笔的商家:
SELECT TRN_MON,STORE_NAME,TRN_CNT
FROM
(SELECT SUBSTR(TRN_DATE,0,6) TRN_MON, ----交易月份
STORE_NAME, ----交易商家
SUM(TRN_STORE) TRN_CNT, ----交易笔数
COUNT(SUBSTR(TRN_DATE,0,6)) OVER(PARTITION BY STORE_NAME) MON_CNT
---交易笔数 > 100的月份数量
FROM STORE_TRN
WHERE TRN_DATE BETWEEN '20170701' AND '20180630' ----近一年内
GROUP BY STORE_NAME ----根据商家进行分组统计
HAVING SUM(TRN_STORE) > 100) ----交易笔数 > 100
WHERE MON_CNT =12 ----近一年内共有12个月交易笔数 > 100