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

26125794 · 发表于 2014-6-23 17:31

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

我觉得union 应该改成union all，不知道我的想法对不对

weipeng825 · 发表于 2014-6-23 17:36

26125794 发表于 2014-6-23 17:31
我觉得union 应该改成union all，不知道我的想法对不对

这个不用，union是记录完全去重，这个值是用户ID重复。
当然union all在这里效率理论上好点点~

szxiaocong · 发表于 2014-6-24 13:00

不错的问题

asc168 · 发表于 2014-6-24 14:14

親測可以
select B.ID, COUNT(DISTINCT A.V)
from TEST A, TEST B
WHERE A.ID <= B.ID
GROUP BY B.ID
ORDER BY ID

asc168 · 发表于 2014-6-24 14:18

想快用這種
select distinct id,
            (select count(distinct b.v)
               from test b
               where b.id <= a.id)
  from test a
order by 1 ;

s6447316512 · 发表于 2014-6-24 14:23

with t as(
select 2014062001 a,'A'b from dual union all
select 2014062001 a,'B'b from dual union all
select 2014062001 a,'A'b from dual union all
select 2014062002 a,'B'b from dual union all
select 2014062002 a,'C'b from dual union all
select 2014062003 a,'A'b from dual union all
select 2014062004 a,'A'b from dual union all
select 2014062004 a,'D'b from dual union all
select 2014062004 a,'E'b from dual union all
select 2014062004 a,'E'b from dual union all
select 2014062005 a,'A'b from dual)
select a as 时间,sum(mark)as 数量 from(
   (select
            a,b,row_number() over(partition by a,b order by a) as mark
            from t
      ))where mark=1
group by a