时间戳

首页 > 解决方案 > 如何使用 row_number、lag/lead、join SQL 解决问题

mysql - 如何使用 row_number、lag/lead、join SQL 解决问题

问题描述

在sql中，我是全新的，但我只需要使用row_number、lag/lead、join来解决这个问题。我已经为这个问题苦苦挣扎了 2 天，但我无法正确解决它。

我们有一个数据表，您需要从以下方面得到答案：

 Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

这是我的代码：

      if object_id('tempdb..#c1') is not null
          drop table #c1

        create table #c1 (datemessage datetime2 ,id_tasks nvarchar(255),Whowritedmessage nvarchar(255));

        insert into #c1 
        values
        ('2017-11-01 04:59:07.614','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:05:17.500','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:07:18.702','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:07:43.128','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:07:59.578','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:09:55.063','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:14:12.587','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:14:56.369','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 06:06:56.108','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 06:07:07.279','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 06:21:17.548','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 06:21:38.428','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent')



        SELECT 
tabC.whowritedmessage,
tabA.datemessage,
tabA.answer,
tabA.whowritedmessage,
tabA.id_tasks
FROM(SELECT 
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
whowritedmessage
FROM #c1
where whowritedmessage ='Client') as tabC
join (SELECT 
              top 50 percent
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
LEAD(datemessage,7) OVER (ORDER BY whowritedmessage) as datemessage,
datemessage as answer,
whowritedmessage,
id_tasks
FROM #c1
) as tabA  on tabA.number = tabC.number

我们有一个数据表，您需要从以下方面得到答案：

 Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

但是我不断得到7行，虽然应该有5行，因为如果客户端走相同的下一行，则不考虑在内，与代理相同

标签： mysqlsqlsql-server

解决方案

检查这个脚本 -

WITH CTE AS
(
    SELECT *,
    LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
    FROM #c1
)

SELECT whowritedmessage,
datemessage,
ans,
ISNULL(who_lag,'Agent') whowritedmessage2,
id_tasks 
FROM
(
    SELECT *,
    LEAD(datemessage) OVER (ORDER BY datemessage) ans,
    LEAD(whowritedmessage) OVER (ORDER BY datemessage) who_lead
    FROM CTE
    WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)A
WHERE whowritedmessage = 'Client'

输出是-

whowritedmessage    datemessage                 ans                          whowritedmessage2  id_tasks
Client              2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

更改： 为了更好地理解，我在此处添加了 1 个 CTE，其输出与上述脚本相同。

WITH CTE AS
(
    SELECT *,
    LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
    FROM #c1
),
CTE2 AS
(
    SELECT  whowritedmessage,   datemessage,    
    LEAD(datemessage) OVER (ORDER BY datemessage) ans,
    ISNULL(who_lag,'Agent') whowritedmessage2,
    id_tasks
    FROM CTE
    WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)

--SELECT * FROM CTE
--SELECT * FROM CTE2
SELECT * FROM CTE2 WHERE whowritedmessage = 'Client'

您可以通过运行一条一条的 SELECT 语句（现已注释）来了解数据是如何一步步转换的。请记住，第 3 条 SELECT 语句是您的最终脚本，而其他 2 条带注释的 SELECT 语句仅供您检查。希望这将帮助您了解整个过程。

推荐阅读