时间戳

首页 > 解决方案 > PostgreSQL提取小时和0总和值分组不显示结果

sql - PostgreSQL提取小时和0总和值分组不显示结果

问题描述

我在 Postgres 中有这个 SQL 请求

SELECT CAST(extract(hour from
                    CAST("public"."v_mdmeddialogues"."datecreation" AS timestamp)) AS integer) AS "datecreation",
       "public"."v_mdmeddialogues"."refspecialty"                                              AS "refspecialty",
       "Refspecialty"."label"                                                                  AS "label",
       COALESCE(count(distinct "public"."v_mdmeddialogues"."iddialogue"), 0)                   AS "count",
       COALESCE(sum("public"."v_mdmeddialogues"."timespent"), 0)                               AS "heures"
FROM "public"."v_mdmeddialogues"
         LEFT JOIN "public"."v_mdiddoctors" "Refuser" ON "public"."v_mdmeddialogues"."refuser" = "Refuser"."iduser"
         LEFT JOIN "public"."v_mdparamspecialties" "Refspecialty"
                   ON "public"."v_mdmeddialogues"."refspecialty" = "Refspecialty"."idspecialty"
WHERE (date_trunc('month', CAST("public"."v_mdmeddialogues"."datecreation" AS timestamp)) BETWEEN date_trunc('month',
                                                                                                             CAST(
                                                                                                                     (CAST(now() AS timestamp) + (INTERVAL '-3 month')) AS timestamp)) AND date_trunc('month', CAST(now() AS timestamp)) AND
       "Refspecialty"."label" = 'Dermatologie' AND
       "public"."v_mdmeddialogues"."codedialogueaction" in ('RESPONSE', 'TRANSFER'))
  AND extract('dow' FROM "public"."v_mdmeddialogues"."datecreation") IN (0, 6)
GROUP BY CAST(extract(hour from CAST("public"."v_mdmeddialogues"."datecreation" AS timestamp)) AS integer),
         "public"."v_mdmeddialogues"."refspecialty", "Refspecialty"."label"
ORDER BY CAST(extract(hour from CAST("public"."v_mdmeddialogues"."datecreation" AS timestamp)) AS integer) ASC,
         "public"."v_mdmeddialogues"."refspecialty" ASC, "Refspecialty"."label" ASC;

问题是在凌晨 4 点到 5 点之间“发生”没有任何价值，因此结果显示： 在此处输入图像描述

我的桌子是：

    create table mdmeddialogues
(
    iddialogue         varchar(255),
    codedialoguetype   varchar(255),
    refdialogueroot    bigint,
    codedialogueaction varchar(255),
    refuser            varchar(255),
    refspecialty       varchar(255),
    datecreation       timestamp,
    refoperator        varchar(255),
    source             varchar(255),
    timespent          bigint,
    datelastupdate     timestamp
);

create table mdparamspecialties
(
    idspecialty    varchar(255),
    code           varchar(255),
    label          varchar(255),
    active         bit,
    exclusive      bit,
    valid          bit,
    datelastupdate timestamp
);

如您所见，结果跳过了 4 中的值datecreation。如何在 和 中显示具有空值 (0) 的这一count行heures？

谢谢您的帮助

标签： sqlpostgresql

解决方案

首先，这与您的问题是分开的，但是v_mdmeddialogues在查询的可读性方面添加别名并进行一些格式化确实有很大帮助。此外，您还有很多不必要的转换，例如将时间戳转换为时间戳。我假设datecreation是一个文本字段，在这种情况下，我将演员留在原地，但如果这是一个日期/时间，那么该演员也是不必要的，甚至有点误导。最后，在我看来，多余的报价会分散注意力。唯一需要引号的时候是在表的 DDL 中出现引号时，我强烈反对在几乎所有情况下都这样做。

无论如何，使用上述观察对原始查询进行快速重构可能如下所示（为简单起见，我将 public.v_mdmeddialogues 别名为“md”）：

SELECT
   extract (hour from md.datecreation) AS datecreation,
   md.refspecialty AS refspecialty,
   Refspecialty.label AS label,
   COALESCE(count(distinct md.iddialogue), 0) AS count,
   COALESCE(sum(md.timespent), 0)  AS heures
FROM
   mdmeddialogues md
   LEFT JOIN mdiddoctors Refuser ON 
     md.refuser = Refuser.iduser
   LEFT JOIN mdparamspecialties Refspecialty
     ON md.refspecialty = Refspecialty.idspecialty
WHERE
  date_trunc('month', md.datecreation) BETWEEN
    date_trunc('month', current_timestamp) - INTERVAL '3 month' AND 
    date_trunc('month', current_timestamp) AND
  Refspecialty.label = 'Dermatologie' AND
  md.codedialogueaction in ('RESPONSE', 'TRANSFER') and
  extract('dow' FROM md.datecreation) IN (0, 6)
GROUP BY
  datecreation, md.refspecialty, Refspecialty.label
ORDER BY
  datecreation ASC, md.refspecialty ASC, Refspecialty.label ASC;

免责声明：我可能把一两件事搞砸了，但希望你能看到这更具可读性和更容易理解。

现在谈谈你的问题。如果您有一个已知的数据集作为输出，您可以生成该数据集，然后左外连接我们的查询。在这种情况下，您似乎想要一个有保证的 0-23。为此，您可以使用 agenerate_series (0, 23)创建这些，然后将其保留下来：

SELECT
   gs.datecreation,
   md.refspecialty AS refspecialty,
   Refspecialty.label AS label,
   COALESCE(count(distinct md.iddialogue), 0) AS count,
   COALESCE(sum(md.timespent), 0)  AS heures
FROM
   generate_series (0, 23) gs (datecreation)
   left join mdmeddialogues md on
     gs.datecreation = extract (hour from md.datecreation) and 
     date_trunc('month', md.datecreation) BETWEEN
        date_trunc('month', current_timestamp) - INTERVAL '3 month' AND 
        date_trunc('month', current_timestamp) AND
     md.codedialogueaction in ('RESPONSE', 'TRANSFER') and
     extract('dow' FROM md.datecreation) IN (0, 6)
   LEFT JOIN mdiddoctors Refuser ON 
     md.refuser = Refuser.iduser
   LEFT JOIN mdparamspecialties Refspecialty on
     md.refspecialty = Refspecialty.idspecialty and
     Refspecialty.label = 'Dermatologie'
GROUP BY
  gs.datecreation, md.refspecialty, Refspecialty.label
ORDER BY
  gs.datecreation ASC, md.refspecialty ASC, Refspecialty.label ASC;

如果您希望refspecialtyandlabel值也显示出来，那是另一回事。由于这两个值之一在 where 子句中是硬编码的，因此您可以将其添加到合并中。

推荐阅读