sql—计算分区上的值更改时的运行总数

qxsslcnc 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(284)

我很难想出如何写一个窗口函数来解决我的问题。我是窗口函数的新手，但我认为可以编写一个来满足我的需要。
问题陈述：
我想计算一个传输序列，显示一个人何时根据相应的位置id改变了位置。
样本数据（表1）

+----------+------------+-----------+---------+
| PersonID | LocationID | Date      | Time    |
+----------+------------+-----------+---------+
| 12       | A          | 6/17/2020 | 12:00PM |
+----------+------------+-----------+---------+
| 12       | A          | 6/18/2020 | 1:00PM  |
+----------+------------+-----------+---------+
| 12       | B          | 6/18/2020 | 6:00AM  |
+----------+------------+-----------+---------+
| 12       | C          | 6/19/2020 | 3:00PM  |
+----------+------------+-----------+---------+
| 13       | A          | 6/16/2020 | 8:00AM  |
+----------+------------+-----------+---------+
| 13       | A          | 6/16/2020 | 11:00AM |
+----------+------------+-----------+---------+
| 13       | A          | 6/16/2020 | 12:00AM |
+----------+------------+-----------+---------+
| 13       | B          | 6/16/2020 | 4:00PM  |
+----------+------------+-----------+---------+

预期结果

+----------+------------+-----------+---------+-------------------+
| PersonID | LocationID | Date      | Time    | Transfer Sequence |
+----------+------------+-----------+---------+-------------------+
| 12       | A          | 6/17/2020 | 12:00PM | 1                 |
+----------+------------+-----------+---------+-------------------+
| 12       | A          | 6/18/2020 | 1:00PM  | 1                 |
+----------+------------+-----------+---------+-------------------+
| 12       | B          | 6/18/2020 | 6:00AM  | 2                 |
+----------+------------+-----------+---------+-------------------+
| 12       | C          | 6/19/2020 | 3:00PM  | 3                 |
+----------+------------+-----------+---------+-------------------+
| 13       | A          | 6/16/2020 | 8:00AM  | 1                 |
+----------+------------+-----------+---------+-------------------+
| 13       | A          | 6/16/2020 | 11:00AM | 1                 |
+----------+------------+-----------+---------+-------------------+
| 13       | A          | 6/16/2020 | 12:00AM | 1                 |
+----------+------------+-----------+---------+-------------------+
| 13       | B          | 6/16/2020 | 4:00PM  | 2                 |
+----------+------------+-----------+---------+-------------------+

我试过的

SELECT 
     [t1].[PersonID]
    ,[t1].[LocationID]
    ,[t1].[Date]
    ,[t1].[Time]
    ,DENSE_RANK() 
         OVER( 
           partition BY [t1].[PersonID], [t1].[LocationID] 
           ORDER BY [t1].[Date] ASC, [t1].[Time] ASC) AS 
       [Transfer Sequence]

FROM Table1 [t1]

不幸的是，我认为无论locationid的值是否改变，都是在分配一个秩。我需要一个函数，将只添加一个序列时，locationid已经改变。
任何帮助都将不胜感激。
谢谢您！

sql window-functions sql-server tsql gaps-and-islands

来源：https://stackoverflow.com/questions/62455473/calculating-a-running-total-of-when-a-value-changes-over-a-partition

2条答案

按热度按时间

s3fp2yjn1#

您希望将“相邻”行放在同一组中。straigt窗口函数不能为您做到这一点-我们需要使用间隙和孤岛技术：

select 
    t.*, 
    sum(case when locationID = lagLocationID then 0 else 1 end) 
        over(partition by personID order by date, time) 
        as transfert_sequence
from (
    select 
        t.*, 
        lag(locationID) 
            over(partition by personID order by date, time) 
            as lagLocationID
    from mytable t
) t

其思想是计算一个窗口和，每次locationid改变时它都会递增。
请注意，当一个人回到他们以前去过的地方时，这将正确地处理这个情况。

赞(0）回复(0）举报 2021-07-26

xa9qqrwz2#

我所做的（我相信这不是最好的方法）是为传输序列（sequence）创建第二个表，其中包含personid、locationid、date、time和空字段，然后是一个游标：

DECLARE transaction CURSOR
FOR select PersonID, LocationID, Date, Time from table1;

然后循环：

OPEN CURSOR transaction
set @count = 0
set @person_saved = ""
set @location_saed = ""
FETCH NEXT FROM transaction INTO @person, @location, @date, @time

WHILE @@FETCH_STATUS = 0  
BEGIN
if @person_saved <> @person  -- changing personID, reset count
begin
set count = 0
set persone_saved = @person
end
if @location_saved <> @location. -- changing location, add count
begin
set @count = @count + 1
set @location_saved = @location
end
update table1 set sequence = @count where PersonId = @person and locationId = @location and date = @date and time = @time

FETCH NEXT FROM transaction INTO @person, @location, @date, @time
END

CLOSE transaction
DEALLOCATE transaction

赞(0）回复(0）举报 2021-07-26

我来回答

sql—计算分区上的值更改时的运行总数

2条答案

相关问题

热门标签

最新问答