mysql - 为 SQL 视图合并两行

Question

这就是我想要做的。我有一些与项目 ID 相关的数据。我需要像这样组合行：

但我不确定如何以有效的方式解决这个问题。我从哪说起呢？我怎样才能像这样将值滚动到另一行？目标是将它放在同一行上，以便可以对它进行进一步的计算，并将数量与我们的用例所需的费用 Id 相对应。

标签： mysqlsnowflake-cloud-data-platform

Answer 1

对你正在寻找的东西做一些假设，你可能想要这样的东西：

SELECT DISTINCT account_id,
       FIRST_VALUE("type") OVER (PARTITION BY account_id ORDER BY STARTDATE) AS "type",
       FIRST_VALUE(qty) OVER (PARTITION BY account_id ORDER BY STARTDATE) AS qty,
       FIRST_VALUE(startdate) OVER (PARTITION BY account_id ORDER BY startdate) AS startdate,
       SUM("plan") OVER (PARTITION BY account_id) AS "plan",
       FIRST_VALUE(charge_id) OVER (PARTITION BY account_id ORDER BY STARTDATE) AS charge_id
FROM your_table;