一个布尔条件计是具备条件的sql月
问题描述:
所以我有这样的一个表:一个布尔条件计是具备条件的sql月
---id---datetime---month---active
1 2016-12-01 Dec-16 0
1 2016-12-02 Dec-16 1
1 2017-01-28 Jan-17 1
1 2017-02-03 Feb-17 0
1 2017-02-24 Feb-17 0
1 2017-03-05 Mar-17 0
1 2017-03-24 Mar-17 1
1 2017-04-02 Apr-17 1
1 2017-04-25 Apr-17 1
1 2017-05-02 May-17 1
1 2017-05-28 May-17 0
我想这样的结果:
---id---monthCount---Active
1 1 0
1 2 1
1 2 0
1 3 1
1 1 0
表确实有更多的比1 id
。
现在我刚刚起步的最小和最大日之间的差异使用由id
,active
和排序由datetime
分区和排名,但是这给了我当它第一次进入到1之间的几个月,最后当它更改为0.我希望它通过active
的每次更改进行详细分隔。
我该如何做到这一点?
答
在SQL Server 2012+,你可以使用LAG
这样
DECLARE @SampleData AS TABLE
(
id int,
[datetime] datetime,
active bit
)
;WITH temp AS
(
SELECT sd.id,
sd.active,
sd.[datetime],
lag(sd.active) over(PARTITION BY sd.id ORDER BY sd.datetime) AS previousActive,
lag(sd.id) over(ORDER BY sd.id, sd.datetime) AS previousId
FROM @SampleData sd
)
,temp1 AS
(
SELECT *,
sum(CASE WHEN t.previousActive IS NULL OR t.previousActive != t.active OR t.id != t.previousId THEN 1
ELSE 0 END)
OVER(PARTITION BY t.id ORDER BY t.[datetime]) AS groupid
FROM temp t
)
SELECT t.id, count(DISTINCT month(t.datetime)) AS monthCount, t.active
FROM temp1 t
GROUP BY id, t.groupid, t.active
答
您可以使用行号差异来识别组(这是一个间隙和孤岛问题)。那么最终的解决方案需要count(distinct)
:
select id, count(distinct month) as monthcount, active
from (select t.*,
row_number() over (partition by id order by month, active) as seqnum_i,
row_number() over (partition by id, active order by month) as seqnum_ia
from t
) t
group by id, active, (seqnum_i - seqnum_ia);
这是假设数据是由ID,月活跃有序的 - 这是问题的数据的排序。真的会更好的是有一个明确定义行排序顺序的列。
什么是括号?当我尝试运行代码时出现错误,因为语法不正确,我从来没有看到可以解释的分组。这是一个错误吗? –