时间戳

首页 > 解决方案 > PostgreSQL 生成丢失的记录并将它们与源表分组

sql - PostgreSQL 生成丢失的记录并将它们与源表分组

问题描述

我正在创建一个 PostgreSQL 查询，我希望自动填写每天丢失的记录。

我突然想到，我可以生成一个包含零值的表，然后将源表连接到它。

所以我创建了这个查询，但结果仍然不包含丢失的天数，只包含源数据库表中的现有记录。例如，缺少“2021-08-01 00:00:00”、“2021-08-07 00:00:00”或“2021-08-08 00:00:00”的记录。

SELECT
  s."Date",
  s."PowerOn",
  s."Idle",
  s."Run",
  CONCAT_WS('%', ROUND(NULLIF(s."Run"::numeric, 0) / NULLIF(s."PowerOn"::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
    SELECT d."Date", bigint '0' AS "PowerOn", bigint '0' AS "Idle", bigint '0' AS "Run", text '0 %' AS "Effectivity" 
    FROM (
        SELECT generate_series(timestamp '2021-08-01 00:00:00'
                        , NOW()
                        , interval  '1 day')::timestamp
) d("Date")) f
JOIN "Absolute_OEE" s ON s."Machine" = 'Machine01'
WHERE
  s."Date" > '2021-08-01 00:00:00'
GROUP BY s."Date",s."PowerOn", s."Idle", s."Run"
ORDER BY s."Date"

结果：

在此处输入图像描述

您能否告诉我如何对记录进行分组并为未记录的日期添加零值？

感谢您的建议和提示。

标签： sqlpostgresqljoin

解决方案

您可以使用 LEFT JOIN 和 COALESCE

SELECT
  d."Date",
  coalesce(s."PowerOn", bigint '0') AS "PowerOn",
  coalesce(s."Idle", bigint '0') AS "Idle",
  coalesce(s."Run", bigint '0') AS "Run",
  CONCAT_WS('%', ROUND(NULLIF(coalesce(s."Run", bigint '0')::numeric, 0) / NULLIF(coalesce(s."PowerOn", bigint '0')::numeric, 0) * 100, 2), '') As "Effectivity"
FROM (
     SELECT generate_series(timestamp '2021-08-01 00:00:00'
                     , NOW()
                     , interval  '1 day')::timestamp
     ) d
LEFT JOIN "Absolute_OEE" s ON d."Date"= s."Date" 
   AND s."Machine" = 'Machine01'
   AND s."Date" > '2021-08-01 00:00:00' 
GROUP BY  d."Date",
  coalesce(s."PowerOn", bigint '0'),
  coalesce(s."Idle", bigint '0'),
  coalesce(s."Run", bigint '0')
ORDER BY d."Date"

推荐阅读