在sql中每两行对同一列进行一次rest，并以秒为单位转换结果

lymgl2op 于 2021-06-24 发布在 Hive

关注(0)|答案(3)|浏览(427)

我需要帮忙收拾我的Hivetable。

DATE               |    ID     |    RESTDATESINSECONDS     

 2019-03-28 10:05:27        1             (2019-03-28 10:05:38)-(2019-03-28 10:05:27)
 2019-03-28 10:05:38        1             (2019-03-28 10:14:14)- (2019-03-28 10:05:38)
 2019-03-28 10:14:14        1             (2019-03-28 10:14:16) -(2019-03-28 10:14:14)
 2019-03-28 10:14:16        1             (2019-03-28 10:14:46) -(2019-03-28 10:14:16)
 2019-03-28 10:14:46        1             (2019-03-28 10:15:30) -(2019-03-28 10:15:30)
 2019-03-28 10:15:30        1                """
     """                    """              """

我有二十万排。我想将此作为：

DATE               |    ID     |    RESTDATESINSECONDS     

 2019-03-28 10:05:27        1             11
 2019-03-28 10:05:38        1             516
 2019-03-28 10:14:14        1             2
 2019-03-28 10:14:16        1             30
 2019-03-28 10:14:46        1             44
 2019-03-28 10:15:30        1             ""

我试过各种各样的括号和计数，但我试过的每种方法都会抛出不同的语法错误！有人有什么想法吗？
非常感谢：）

sql Hive hiveql hql

来源：https://stackoverflow.com/questions/56394973/rest-every-two-rows-of-the-same-column-in-sql-and-and-convert-the-result-in-seco

3条答案

按热度按时间

8gsdolmq1#

with cte_test_table
as
(select * from
  ( values('2019-03-28 10:05:27',1)
         ,('2019-03-28 10:05:38',1)
         ,('2019-03-28 10:14:14',1)
         ,('2019-03-28 10:14:16',1)
         ,('2019-03-28 10:14:46',1)
         ,('2019-03-28 10:15:30',1)
  ) as t([Date],ID)
)

select ID, [Date]
,datediff(ss,[Date],LAG ([Date], 1, [Date]) over (partition by ID order by [Date] desc)) as RESTDATESINSECONDS
from cte_test_table
order by [Date]

赞(0）回复(0）举报 2021-06-24

djp7away2#

如果不保证要从中获得时间差的行是连续的，可以使用以下方法进行操作（在任何情况下都有效）：

select 
  t.id,
  t.date,
  datediff(
    s, 
    t.date, 
    (select min(date) from tablename where id = t.id and date > t.date)
  ) restdateinseconds
from tablename t

请看演示。
结果：

> id | date                | restdateinseconds
> -: | :------------------ | ----------------:
>  1 | 28/03/2019 10:05:27 |                11
>  1 | 28/03/2019 10:05:38 |               516
>  1 | 28/03/2019 10:14:14 |                 2
>  1 | 28/03/2019 10:14:16 |                30
>  1 | 28/03/2019 10:14:46 |                44
>  1 | 28/03/2019 10:15:30 |

赞(0）回复(0）举报 2021-06-24

2ledvvac3#

使用 lead() 函数获取下一行 date 以及 unix_timestamp() 将日期转换为秒，然后减去：

with test_data as (
select stack(6,
'2019-03-28 10:05:27',
'2019-03-28 10:05:38',
'2019-03-28 10:14:14',
'2019-03-28 10:14:16',
'2019-03-28 10:14:46',
'2019-03-28 10:15:30') as `date`
)

select `date`, unix_timestamp(lead(`date`) over(order by `date`)) - unix_timestamp(`date`) as restdateinseconds
  from test_data;

退货：

date                 restdateinseconds
2019-03-28 10:05:27    11   
2019-03-28 10:05:38    516  
2019-03-28 10:14:14    2    
2019-03-28 10:14:16    30   
2019-03-28 10:14:46    44   
2019-03-28 10:15:30    NULL

赞(0）回复(0）举报 2021-06-24

我来回答

在sql中每两行对同一列进行一次rest，并以秒为单位转换结果

3条答案

相关问题

热门标签

最新问答