统计相关

-- 需求：求出下表指定日期的每个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