时间戳

首页 > 解决方案 > 如何使用数值在 SQL 中将长格式转换为宽格式

sql - 如何使用数值在 SQL 中将长格式转换为宽格式

问题描述

我有一个看起来像这样的数据集

doc    date     value
2345    201902  470942
2345    201903  470044
2345    201904  470
2345    201905  35000 ...

我想像这样转变

doc    date    value    value_1m    value_2m    value_3m
2345    201905  35000   470         470044      470942
2345    201904  470     470044      470942      ...

如您所见，新列 value_1m、value_2m、value_3m 是前几个月的值 201904、201903、201902 等。

我已经尝试使用（CASE WHEN key），但我的变量“日期”是一个数字，每个月都会使用，所以我不能使用它。

我是这个论坛的新手，所以如果不太清楚，请见谅，并在此先感谢。

标签： sqlpivotimpala

解决方案

在 Impala 上，你可以尝试这样的事情

你的数据表

+---------------------+-----------------------+-----------------------+--+
| doc_date_value.doc  | doc_date_value.cdate  | doc_date_value.value  |
+---------------------+-----------------------+-----------------------+--+
| 2345                | 201902                | 470942                |
| 2345                | 201903                | 470044                |
| 2345                | 201904                | 470                   |
| 2345                | 201905                | 35000                 |
+---------------------+-----------------------+-----------------------+--+

带窗口函数的查询和多个子查询

WITH t2 AS(
WITH t AS(
SELECT *, LEAD(value,1,0) OVER(PARTITION BY doc ORDER BY cdate DESC) as value_1m 
FROM doc_date_value
ORDER BY cdate DESC)
SELECT doc, cdate,value, value_1m,
       LEAD(value_1m,1,0) OVER(PARTITION BY doc ORDER BY cdate DESC) as value_2m 
FROM t)
SELECT doc, cdate,value, value_1m, value_2m,
       LEAD(value_2m,1,0) OVER(PARTITION BY doc ORDER BY cdate DESC) as value_3m 
FROM t2;

预期产出

+------+--------+--------+----------+----------+----------+
| doc  | cdate  | value  | value_1m | value_2m | value_3m |
+------+--------+--------+----------+----------+----------+
| 2345 | 201905 | 35000  | 470      | 470044   | 470942   |
| 2345 | 201904 | 470    | 470044   | 470942   | 0        |
| 2345 | 201903 | 470044 | 470942   | 0        | 0        |
| 2345 | 201902 | 470942 | 0        | 0        | 0        |
+------+--------+--------+----------+----------+----------+

您可以在 Impala 或 Hive 上运行此查询。

推荐阅读