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

newkid

赞一下peter,支持苏上人当反面教材。

苏上人

newkid 发表于 2014-6-25 23:19
赞一下peter,支持苏上人当反面教材。

我都成反面教材啦  哎

weipeng825

newkid 发表于 2014-6-25 23:19
赞一下peter,支持苏上人当反面教材。

版主大神，来个千万级速度点的算法撒

peter1166

weipeng825 发表于 2014-6-26 09:29
版主大神，来个千万级速度点的算法撒

8楼写法不行？

newkid

CREATE TABLE  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 a,SUM(SUM(DECODE(rn,1,1))) OVER(ORDER BY a) cnt
FROM (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY b ORDER BY a) rn FROM t)
GROUP BY a;

         A        CNT
---------- ----------
         1          2
         2          3
         3          3
         4          5
         5          5

         
用分组聚合：
SELECT a,SUM(COUNT(*)) OVER(ORDER BY a) cnt
FROM (SELECT b,MIN(a) a FROM t GROUP BY b)
GROUP BY a;

         A        CNT
---------- ----------
         1          2
         2          3
         4          5

有些日期缺失了，如果要补就用外连接：

SELECT t1.a,LAST_VALUE(t2.cnt IGNORE NULLS) OVER(ORDER BY t1.a)
  FROM  (SELECT DISTINCT a FROM t) t1
        LEFT JOIN
        (SELECT a,SUM(COUNT(*)) OVER(ORDER BY a) cnt
         FROM (SELECT b,MIN(a) a FROM t GROUP BY b)
        GROUP BY a
        ) t2
ON t1.a=t2.a;

西雁大大

newkid 发表于 2014-6-26 22:33
CREATE TABLE  t as
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union

赞！和大神想到一起了

weipeng825

peter1166 发表于 2014-6-26 12:47
8楼写法不行？

这个是可以的，我还想参考其他的算法

我实际处理中还有好几个维度，还要做cube维度汇总运算。
这样的话，row_number标记就不可以复用了。。。。

weipeng825

newkid 发表于 2014-6-26 22:33
CREATE TABLE  t as
select 1 a,'A'b from dual union
select 1 a,'B'b from dual union

版主大人，实际处理中还有好几个维度，最后还要做cube维度汇总运算。
这样的话，row_number标记， 前面group分组都不怎么行。。。。。。

西雁大大

drop table ms_test;
--建表
create table ms_test(id number,
name varchar2(200));

--随机插入1w行
begin
  for i in 1..10000 loop
      insert into ms_test values(ceil(dbms_random.value(1,10)),ceil(dbms_random.value(1,20000)));
  end loop;
end;
/

select * from ms_test;

--------------------------------------
--方法一 先去重，取id最小的一条。然后外层用分析函数。
select mid, sum(cn) over(order by mid) n
  from (select mid, count(name) cn
          from (select min(id) mid, name from ms_test group by name)
         group by mid);
--用时0.219s

--方法二--     
   
with s as(select a.id id2,t.name from ms_test t,ms_test a
where t.id<=a.id)
select id2,count(distinct name)c from s group by id2
order by id2;

--耗时34.944s,非等值自连接虽然思路巧妙，但是数据量大的时候，计算过程不可承受

newkid

weipeng825 发表于 2014-6-27 11:19
版主大人，实际处理中还有好几个维度，最后还要做cube维度汇总运算。
这样的话，row_number标记， 前面g ...

举出一个多维度的例子。
DISTINCT是无法汇总的，有可能每天数字是1, 月总数也是1 (因为整个月就是同一个人)这个必须先弄清楚。