统计相关

-- 需求：求出下表指定日期的每个TYPE_ID的新增客户和累计新增客户，
-- 20110703的新增客户=去重(20110703的手机号 MINUS (20110701+20110702的手机号))
-- 20110703累计新增客户 = 去重((20110703+20110702)的手机号 MINUS 20110701的手机号))
SQL> select * from tab;

DATE_ID              TYPE_ID              USER_PHONE
-------------------- -------------------- --------------------
20110701             T恤                  13400000000
20110701             T恤                  13400000001
20110701             短裤                 13400000001
20110701             短裤                 13400000005
20110702             T恤                  13400000000
20110702             T恤                  13400000002
20110702             短裤                 13400000002
20110702             短裤                 13400000003
20110703             T恤                  13400000003
20110703             T恤                  13400000004
20110703             短裤                 13400000003
20110703             短裤                 13400000005

12 rows selected

SQL> 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