【讨论】求SQL-----累计统计去重用户数

weipeng825

newkid 发表于 2014-6-27 22:10
举出一个多维度的例子。
DISTINCT是无法汇总的，有可能每天数字是1, 月总数也是1 (因为整个月就是同一个 ...

CREATE TABLE T_TIME AS
             SELECT TO_NUMBER($vDate_CD || '01') AS  DATE_HR_CD FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '02')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '03')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '04')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '05')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '06')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '07')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '08')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '09')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '10')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '11')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '12')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '13')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '14')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '15')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '16')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '17')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '18')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '19')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '20')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '21')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '22')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '23')  FROM DUAL
   UNION ALL SELECT TO_NUMBER($vDate_CD || '24')  FROM DUAL;

DELETE FROM BIEDS.TW_FLOW_ACCSTAT_HR_SUM where DATE_CD = $vDate_CD;

--累计指标
INSERT /*+ APPEND */ INTO TW_FLOW_ACCSTAT_HR_SUM(
   DATE_HR_CD
  ,AREA_CODE
  ,PLAT_TYPE
  ,TOTAL_USER_CNT
  ,TOTAL_LOG_USER_CNT
  ,TOTAL_PAGE_CNT
  ,TOTAL_LOG_PAGE_CNT
   )
  SELECT DATE_HR_CD
        ,NVL(AREA_ID, '0')
        ,NVL(PLAT_ID, '0')
        ,COUNT(DISTINCT NVL(USER_ID, UCODE)) AS TOTAL_USER_CNT
        ,COUNT(DISTINCT USER_ID) AS TOTAL_LOG_USER_CNT
        ,COUNT(*) AS TOTAL_PAGE_CNT
        ,COUNT(USER_ID) AS TOTAL_LOG_PAGE_CNT
    FROM (SELECT T2.DATE_HR_CD, T1.AREA_ID, T1.PLAT_ID, T1.USER_ID, T1.UCODE
            FROM BIEDS.TW_USER_ACC_DTL T1, T_TIME T2
           WHERE T1.DATE_CD <= $vDate_CD
             AND T1.DATE_HR_CD <= T2.DATE_HR_CD)
   GROUP BY DATE_HR_CD ,CUBE(AREA_ID, PLAT_ID);
COMMIT;


上面就是代码示范，因为要求一天24小时的累计汇总。就想了个笨办法，搞了个当天24小时的维表，关联数据翻24倍。。。。。
但实在想不到别的好办法了~

lastwinner

$vDate_CD ？

weipeng825

lastwinner 发表于 2014-7-1 10:23
$vDate_CD ？

呵呵，脚本里面直接copy出来的   yyyymmdd格式

li0924

〇〇 发表于 2014-6-22 17:31
with t as(
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union

这题目有点意思；纯属娱乐！

1. $ awk '{if(! c[$2]++)s++;a[$1]=s}END{for(i=1;i<=asorti(a,b);i++)print b[i],a[b[i]]}' lottu
   
2. 1 2
   
3. 2 3
   
4. 3 3
   
5. 4 5
   
6. 5 5
   
7. $ cat -v lottu
   
8. 1 A
   
9. 1 B
   
10. 1 A
    
11. 2 B
    
12. 2 C
    
13. 3 A
    
14. 4 A
    
15. 4 D
    
16. 4 E
    
17. 5 A

复制代码

Skye_to_Skye

peter1166 发表于 2014-6-22 18:28
with t as(
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union

这里面省略了partition by 能解释下么？

peter1166

Skye_to_Skye 发表于 2014-7-1 18:36
这里面省略了partition by 能解释下么？

分解看，应该能明白吧。


with t as(
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union
select 1 a,'A'b from dual union
select 2 a,'B'b from dual union
select 2 a,'C'b from dual union
select 3 a,'A'b from dual union
select 4 a,'A'b from dual union
select 4 a,'D'b from dual union
select 4 a,'E'b from dual union
select 5 a,'A'b from dual
)
select t.*
           ,row_number()over(partition by b order by a) rn
from t;
   
         A B         RN
---------- - ----------
         1 A          1
         3 A          2
         4 A          3
         5 A          4
         1 B          1
         2 B          2
         2 C          1
         4 D          1
         4 E          1

with t as(
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union
select 1 a,'A'b from dual union
select 2 a,'B'b from dual union
select 2 a,'C'b from dual union
select 3 a,'A'b from dual union
select 4 a,'A'b from dual union
select 4 a,'D'b from dual union
select 4 a,'E'b from dual union
select 5 a,'A'b from dual
)
select t1.a ,t1.b
        ,rn
        ,decode(rn,1,1,0)
       ,sum(decode(rn,1,1,0))over( order  by a, b) b_cnt
from (
        select t.*
               ,row_number()over(partition by b order by a) rn
        from t
     ) t1
         A B         RN DECODE(RN,1,1,0)      B_CNT
---------- - ---------- ---------------- ----------
         1 A          1                1          1
         1 B          1                1          2
         2 B          2                0          2
         2 C          1                1          3
         3 A          2                0          3
         4 A          3                0          3
         4 D          1                1          4
         4 E          1                1          5
         5 A          4                0          5
         
with t as(
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union
select 1 a,'A'b from dual union
select 2 a,'B'b from dual union
select 2 a,'C'b from dual union
select 3 a,'A'b from dual union
select 4 a,'A'b from dual union
select 4 a,'D'b from dual union
select 4 a,'E'b from dual union
select 5 a,'A'b from dual
)
select t2.a ,max(b_cnt) b_cnt
from (
        select t1.a ,t1.b
               ,sum(decode(rn,1,1,0))over( order  by a, b) b_cnt
        from (
                select t.*
                       ,row_number()over(partition by b order by a) rn
                from t
             ) t1
       ) t2
group by t2.a;     
          A      B_CNT
---------- ----------
         1          2
         2          3
         3          3
         4          5
         5          5
           

Skye_to_Skye

peter1166 发表于 2014-7-1 19:00
分解看，应该能明白吧。

很感谢您的回答。。。。谢谢

angu204

1. with t as(
   
2. select 1 a,'A'b from dual union
   
3. select 1 a,'B'b from dual union
   
4. select 1 a,'A'b from dual union
   
5. select 2 a,'B'b from dual union
   
6. select 2 a,'C'b from dual union
   
7. select 3 a,'A'b from dual union
   
8. select 4 a,'A'b from dual union
   
9. select 4 a,'D'b from dual union
   
10. select 4 a,'E'b from dual union
    
11. select 5 a,'A'b from dual),
    
12. t1 AS (SELECT a ,CASE WHEN b=pre_b THEN
    
13. 0
    
14. ELSE
    
15. 1
    
16. END RESULT
    
17. FROM (
    
18. SELECT a,b,lag(b) over(ORDER BY b,a) pre_b FROM t ORDER BY b)
    
19. ORDER BY a)
    
20. SELECT DISTINCT  a,SUM(RESULT) over(ORDER BY a) counts FROM t1
    
21. ORDER BY a;
    
22. 
    

复制代码

newkid

多维度就多用几次分析函数吗：

CREATE TABLE  t as
select 1 a,'A'b,'Q'c from dual union
select 1 a,'B'b,'W'c from dual union
select 1 a,'A'b,'W'c from dual union
select 2 a,'B'b,'Q'c from dual union
select 2 a,'C'b,'S'c from dual union
select 3 a,'A'b,'R'c from dual union
select 4 a,'A'b,'T'c from dual union
select 4 a,'D'b,'Q'c from dual union
select 4 a,'E'b,'V'c from dual union
select 5 a,'A'b,'W'c from dual;


SELECT a
      ,SUM(SUM(DECODE(rn,1,1))) OVER(ORDER BY a) cntb
      ,SUM(SUM(DECODE(rn2,1,1))) OVER(ORDER BY a) cntc
FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY b ORDER BY a) rn
            ,ROW_NUMBER() OVER(PARTITION BY c ORDER BY a) rn2
        FROM t
      )
GROUP BY a;

szsam009

先按时间，用户名查询唯一记录
再只保留最小的一条记录，
再进行累加
应该可以满足这个要求。