amazon-web-services - 我希望以第一行服务日期的方式从两行或多行获取日期差异 - 第二行服务开始日期，以便我可以获得差异

Question

我的数据看起来像这样

用户身份	完成时间	服务期从	服务期至
00002cd9-94eb-4c06-a2c4-75253fd541b9	2020-11-25T14:20:04.293Z	2020-11-25T14:20:04.200Z	2021-02-25T14:20:04.200Z
00002cd9-94eb-4c06-a2c4-75253fd541b9	2021-03-21T10:27:34.842Z	2021-03-21T10:27:34.800Z	2022-03-21T10:27:34.800Z
00002cd9-94eb-4c06-a2c4-75253fd541b9	2020-07-24T11:22:12.410Z	2020-07-24T11:22:12.300Z	2020-10-24T11:22:12.300Z

serviceperiodto我需要与第一行日期的日期差异- 第二行serviceperiodfrom的日期，它会进行尽可能多的迭代，因为它为每个用户 ID 提供了这些详细信息

请帮助我我尝试使用子查询加入表尝试创建数据透视表但它们似乎都不适合我请帮助

标签： amazon-web-servicesamazon-athena

Answer 1

您可以使用lag/lead访问上一个/下一个项目：

WITH dataset
AS (SELECT *
    FROM
    (
        VALUES
            (1, from_iso8601_timestamp('2020-11-25T14:20:04.200Z'), from_iso8601_timestamp('2021-02-25T14:20:04.200Z')),
            (1, from_iso8601_timestamp('2021-03-21T10:27:34.800Z'), from_iso8601_timestamp('2022-03-21T10:27:34.800Z')),
            (1, from_iso8601_timestamp('2020-07-24T11:22:12.300Z'), from_iso8601_timestamp('2020-10-24T11:22:12.300Z'))
    ) AS t (userid, serviceperiodfrom, serviceperiodto)
   )

SELECT date_diff(
    'hour', 
    serviceperiodto, 
    lead(serviceperiodfrom, 1) OVER (PARTITION BY userid ORDER BY serviceperiodfrom))
FROM dataset

输出：

_col0
770
572