代码之家 › 专栏 › 技术社区 › Alex

选择每周不同和每周不同总体

distinct sql-server

Alex · 技术社区 · 7 年前

我有一个例子 sql fiddle . 我想做的是划分总体 COUNT(DISTINCT ID) 按每周 计数(不同ID) . 例如,如果我有以下概念设置 result 应该是。

year week id_set       overall_distinct   week_distinct  result
2016   1  A,A,A,B,B,C    0                   3            0
2016   2  A,B,C,C,D      1                   4           .25
2016   3  A,B,C,E,F      2                   5           .4

sql FIDLE上链接到的表具有以下架构。而且,实际上我确实有多个“年”值。

CREATE TABLE all_ids
    ([year] int, [week] int, [id] varchar(57))
;
INSERT INTO all_ids
    ([year], [week], [id])
VALUES
    (2016, 1, 'A'),
    (2016, 1, 'A'),
    (2016, 1, 'A'),
    (2016, 1, 'B'),
    (2016, 1, 'B'),
    (2016, 1, 'C'),
    (2016, 2, 'A'),
    (2016, 2, 'B'),
    (2016, 2, 'C'),
    (2016, 2, 'C'),
    (2016, 2, 'D'),
    (2016, 3, 'A'),
    (2016, 3, 'B'),
    (2016, 3, 'C'),
    (2016, 3, 'E'),
    (2016, 3, 'F')
;

编辑

很抱歉给您带来了混淆。上表只是一个概念性的结果示例。实际结果只需如下所示。

year week  overall_distinct   week_distinct  result
2016   1     0                   3            0
2016   2     1                   4           .25
2016   3     2                   5           .4

无需包括 id_set

3 回复 | 直到 7 年前

uzi 7 年前

我用过 dense_rank 和 max() over () 要模拟 count (distinct ...) 具有窗口功能。您可以尝试使用另一个子查询来执行此操作

select
    year, week
    , id_set = stuff((
        select
            ',' + a.id
        from
            all_ids a
        where
            a.year = t.year
            and a.week = t.week
        order by a.id
        for xml path('')
    ), 1, 1, '')
    , overall_distinct = count(case when cnt = 1 then 1 end)
    , week_distinct = count(distinct id)
    , result = cast(count(case when cnt = 1 then 1 end) * 1.0 / count(distinct id) as decimal(10, 2))
from (
    select
        year, week, id, cnt = max(dr) over (partition by id)
    from (
        select 
            *, dr = dense_rank() over (partition by id order by year, week)
        From 
            all_ids
    ) t
) t
group by year, week

输出

year    week    id_set         overall_distinct    week_distinct   result
--------------------------------------------------------------------------
2016    1       A,A,A,B,B,C    0                   3               0.00
2016    2       A,B,C,C,D      1                   4               0.25
2016    3       A,B,C,E,F      2                   5               0.40

Rigerta 7 年前

这是一种方式,可能不是最好的方式:

;with weekly as 
(
    select year, week, count(distinct id) nr 
    from all_ids 
    group by year, week
),
overall as
(
    select a.week, count(distinct a.id) nr 
    from all_ids a 
    where a.id not in (select id from all_ids where week <> a.week and id = a.id ) 
    group by week 

)
select distinct a.year
     , a.week
     , stuff((select ', ' + id 
              from all_ids
              where year = a.year and week = a.week
              for xml path('')), 1, 1, '') ids
     , w.Nr weeklyDistinct
     , isnull(t.Nr, 0) overallDistinct
from all_ids a join weekly w on a.year = w.year and a.week = w.week
               left join overall t on t.week = a.week

paparazzo 7 年前

仅一条语句计数

declare @t table (y int, w int, id varchar(57));
INSERT @t (y, w, id)
VALUES
    (2016, 1, 'A'),
    (2016, 1, 'A'),
    (2016, 1, 'A'),
    (2016, 1, 'B'),
    (2016, 1, 'B'),
    (2016, 1, 'C'),
    (2016, 2, 'A'),
    (2016, 2, 'B'),
    (2016, 2, 'C'),
    (2016, 2, 'C'),
    (2016, 2, 'D'),
    (2016, 3, 'A'),
    (2016, 3, 'B'),
    (2016, 3, 'C'),
    (2016, 3, 'E'),
    (2016, 3, 'F');

select t1.w, count(distinct t1.id) as wk
     , (count(distinct t1.id) - count(distinct t2.id)) as [all]
     , (cast(1 as smallmoney) - cast(count(distinct t2.id) as smallmoney)  / count(distinct t1.id)) as [frac]
from @t t1
left join @t t2 
  on t2.id = t1.id 
 and t2.w <> t1.w 
group by t1.w
order by t1.w;