SQL频率计数

Question

我在SSMS有一张桌子：

Id  Date    Value
111 1/1/18  x
111 1/2/18  x
111 1/3/18  y
111 1/4/18  y
111 1/5/18  x
111 1/6/18  x
222 1/3/18  z
222 1/6/18  y
222 1/8/18  y

我想数一下最近值的频率。因此，输出将是：

Id  Value  Days
111 x      2   *(for 1/5/18 & 1/6/18)*
222 y      3   *(for 1/6/18 & 1/8/18; Here I assume 1/7/18 is a weekend or holiday.  Even though my table skips the weekend, we still want to count days for the weekend)*

这是怎么做的？非常感谢！

Answer 1

使用lag获取上一行的值，然后使用运行的和分配组。然后计算第一组中的数字。

select id,val,datediff(day,min(date),max(date))+1 as days
from (select t.*,sum(case when val=prev_val then 0 else 1 end) over(partition by id order by date desc) as grp
      from (select t.*,lag(val) over(partition by id order by date desc) as prev_val
            from tbl t
           ) t
     ) t
where grp=1
group by id,val

Answer 2

尝试：

SELECT COUNT(*) FROM Table1 WHERE Value = 
(
    SELECT Value FROM Table1 WHERE Id = MAX(Id)
)

Answer 3

我希望你想要这个

select Id, count(Date) as "Days", Value from SSMS
group by ID, Value

如果我错了就纠正我