时间戳

首页 > 解决方案 > Redshift - 有没有办法根据公共列合并行

sql - Redshift - 有没有办法根据公共列合并行

问题描述

我有一张这样的桌子

Node_id | channel       | timestamp           | value                       | folder_id
242278  | return_status | 2020-07-28 14:29:26 | "Return Created"            | 48486
242278  | return_reason | 2020-07-28 14:29:26 | "Customer Opt Out"          | 48486
242278  | return_date   | 2020-07-28 14:29:26 | "2020-07-28T10:29:26-04:00" | 48486
242281  | return_status | 2020-07-28 15:17:31 | "Return Created"            | 34195
242281  | return_reason | 2020-07-28 15:17:31 | "Customer Opt Out"          | 34195
242281  | return_date   | 2020-07-28 15:17:31 | "2020-07-28T11:17:30-04:00" | 34195
242283  | return_status | 2020-07-28 17:08:07 | "Return Created"            | 48896
242283  | return_date   | 2020-07-28 17:08:07 | "2020-07-28T13:08:06-04:00" | 48896
242283  | return_reason | 2020-07-28 17:08:07 | "Poor quality / Faulty"     | 48896

有没有办法在单个查询中执行此操作，或者我需要为每个不同的通道创建一个 CTE，然后创建一个连接？

提前感谢您的所有帮助！我正在尝试创建一个查询，让我可以根据 node_id 将行合并为一行，如下所示

Node_id | return_status    | return_reason           | return_date                | folder_id
242278  | "Return Created" | "Customer Opt Out"      | "2020-07-28T10:29:26-04:00"| 48486
242281  | "Return Created" | "Customer Opt Out"      | "2020-07-28T11:17:30-04:00"| 34195
242283  | "Return Created" | "Poor quality / Faulty" | "2020-07-28T13:08:06-04:00"| 48896

标签： sqlpostgresqlpivotamazon-redshift

解决方案

您可以进行条件聚合：

select
    node_id,
    max(case when channel = 'return_status' then value end) return_status,
    max(case when channel = 'return_reason' then value end) return_reason,
    max(case when channel = 'return_date'   then value end) return_date,
    folder_id
from mytable
group by node_id, folder_id

推荐阅读