统计相关
-- 需求：求出下表指定日期的每个TYPE_ID的新增客户和累计新增客户，-- 20110703的新增客户=去重(20110703的手机号 MINUS (20110701+20110702的手机号))-- 20110703累计新增客户 = 去重((20110703+20110702)的手机号 MINUS 20110701的手机号))SQL> select * from tab;DATE_ID TYPE_ID USER_PHONE-------------------- -------------------- --------------------20110701 T恤 1340000000020110701 T恤 1340000000120110701 短裤 1340000000120110701 短裤 1340000000520110702 T恤 1340000000020110702 T恤 1340000000220110702 短裤 1340000000220110702 短裤 1340000000320110703 T恤 1340000000320110703 T恤 1340000000420110703 短裤 1340000000320110703 短裤 1340000000512 rows selectedSQL> SELECT m.type_id, 2 SUM(CASE WHEN (m.rn = 1 AND m.date_id = '20110703') THEN 1 ELSE 0 END) new_num, 3 SUM(CASE WHEN (m.rn = 1 AND m.date_id > m.min_date AND m.date_id <= '20110703') THEN 1 ELSE 0 END) all_new_num 4 FROM ( 5 SELECT t.date_id, 6 t.type_id, 7 ROW_NUMBER() OVER(PARTITION BY t.type_id,t.user_phone ORDER BY t.date_id) rn, 8 MIN(t.date_id) over(PARTITION BY t.type_id ORDER BY t.date_id) min_date 9 FROM tab t 10 ) m GROUP BY m.type_id 11 ;TYPE_ID NEW_NUM ALL_NEW_NUM-------------------- ---------- -----------T恤 2 3短裤 0 2
