时间戳

首页 > 解决方案 > 根据条件连接多行的列值

postgresql - 根据条件连接多行的列值

问题描述

我有一个格式像这样的表（id 是 pk）

id|timestamps         |year|month|day|groups_ids|status |SCHEDULED          |uid|
--|-------------------|----|-----|---|----------|-------|-------------------|---|
 1|2021-02-04 17:18:24|2020|    8|  9|         1|OK     |2020-08-09 00:00:00|  1|
 2|2021-02-04 17:18:09|2020|    9|  9|         1|OK     |2020-09-09 00:00:00|  1|
 3|2021-02-04 17:19:51|2020|   10|  9|         1|HOLD   |2020-10-09 00:00:00|  1|
 4|2021-02-04 17:19:04|2020|   10| 10|         2|HOLD   |2020-10-09 00:00:00|  1|
 5|2021-02-04 17:18:30|2020|   10| 11|         2|HOLD   |2020-10-09 00:00:00|  1|
 6|2021-02-04 17:18:57|2020|   10| 12|         2|OK     |2020-10-09 00:00:00|  1|
 7|2021-02-04 17:18:24|2020|    8|  9|         1|HOLD   |2020-08-09 00:00:00|  2|
 8|2021-02-04 17:18:09|2020|    9|  9|         2|HOLD   |2020-09-09 00:00:00|  2|
 9|2021-02-04 17:19:51|2020|   10|  9|         2|HOLD   |2020-10-09 00:00:00|  2|
10|2021-02-04 17:19:04|2020|   10| 10|         2|HOLD   |2020-10-09 00:00:00|  2|
11|2021-02-04 17:18:30|2020|   10| 11|         2|HOLD   |2020-10-09 00:00:00|  2|
12|2021-02-04 17:18:57|2020|   10| 12|         2|HOLD   |2020-10-09 00:00:00|  2|

工作是当状态为 OK 时，我想提取每个 uid 的每个 group_ids，按 SCHEDULED 升序排序，如果在 uid 的记录中没有找到 OK，它将根据年月日进行最新的 HOLD。之后，我想对每个 group_ids 进行加权评分：

group_ids > score
1 > 100
2 > 80
3 > 60
4 > 50
5 > 10
6 > 50
7 > 0

因此，如果 [1,1,2] 将更改为 (100+100+80) = 280，它将如下所示：

ids|uid|pattern|score|
---|---|-------|-----|
  1|  1|[1,1,2]|  280|
  2|  2|[2]    |   80|

这很难，因为我在 PostgreSQL 中找不到任何运算符，例如 python for loop 和 append 运算符

标签： postgresql

解决方案

分步演示：db<>fiddle

SELECT 
    s.uid, s.values,
    sum(v.value) as score
FROM (
    SELECT DISTINCT ON (uid)
        uid,
        CASE 
            WHEN cardinality(ok_count) > 0 THEN ok_count
            ELSE ARRAY[last_value]
        END as values
    FROM (
        SELECT 
            *,
            ARRAY_AGG(groups_ids) FILTER (WHERE status = 'OK') OVER (PARTITION BY uid ORDER BY scheduled)as ok_count,
            first_value(groups_ids) OVER (PARTITION BY uid ORDER BY year, month DESC) as last_value
        FROM mytable
    ) s
    ORDER BY uid, scheduled DESC
) s,
  unnest(values) as u_group_id
  JOIN (VALUES 
      (1, 100), (2, 80), (3, 60), (4, 50), (5,10), (6, 50), (7, 0)
  ) v(group_id, value) ON v.group_id = u_group_id 
GROUP BY s.uid, s.values

呼……相当复杂。让我们看一下步骤：

一种）

SELECT 
    *,
    -- 1:
    ARRAY_AGG(groups_ids) FILTER (WHERE status = 'OK') OVER (PARTITION BY uid ORDER BY scheduled)as oks,
    -- 2:
    first_value(groups_ids) OVER (PARTITION BY uid ORDER BY year, month DESC) as last_value
FROM mytable
  1. 使用array_agg()window 函数创建一个 group_ids 数组，而不会像使用 simple 那样丢失其他数据GROUP BY。该FILTER子句仅将 status = OK 记录放入数组中。
  2. first_value()使用窗口函数查找组（分区）的最后一个 group_id 。按降序返回最后一个值。

b)

SELECT DISTINCT ON (uid)                               -- 2
    uid,
    CASE                                               -- 1
        WHEN cardinality(ok_count) > 0 THEN ok_count
        ELSE ARRAY[last_value]
    END as values
FROM (
    ...
) s
ORDER BY uid, scheduled DESC                           -- 2
  1. 该CASE子句要么采用先前创建的数组（来自步骤a1），要么，如果没有，则采用最后一个值（来自步骤a2），创建一个单元素数组。
  2. 该DISTINCT ON子句仅返回有序组的第一个元素。该组是您uid的，顺序由 column 给出scheduled。由于您不想要组内的第一条记录，而是最后一条记录，因此您必须对其进行排序DESC以使最近的记录成为最上面的记录。这是由DISTINCT ON

C）

SELECT 
    uid,
    group_id
FROM (
    ...
) s,
  unnest(values) as group_id   -- 1
  1. 应该将数组提取到每个元素一个记录中。这有助于稍后加入加权值。

d)

SELECT 
    s.uid, s.values,
    sum(v.weighted_value) as score                             -- 2
FROM (
    ...
) s,
  unnest(values) as u_group_id
  JOIN (VALUES 
      (1, 100), (2, 80), ...
  ) v(group_id, weighted_value) ON v.group_id = u_group_id     -- 1
GROUP BY s.uid, s.values                              -- 2
  1. 在数组元素上加入您的加权值。自然地，这可以是一个表或查询或其他任何东西。
  2. 重新组合组以uid计算sSUM()weighted_value

附加说明：

您应该避免重复数据存储。您不需要存储日期部分year，如果您还存储完整日期month。day您始终可以从日期开始计算它们。

推荐阅读