时间戳

首页 > 解决方案 > 需要将 MySQL 查询转换为 PostgreSQL

mysql - 需要将 MySQL 查询转换为 PostgreSQL

问题描述

我有一张有活动的公司表。我需要创建一个汇总表，按月显示活跃公司，这意味着有多少公司每个月至少有一个或多个事件。任何在一个月内有活动的公司都被认为是活跃的。

最后，我能够使用活跃公司的汇总来完成这个详细的计数表，但我无法在 PostgreSQL 中重新创建它。

此外，这份报告列出了所有有事件的公司，我还想要一份只显示总数的报告。

CREATE TABLE mysql_test ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
company VARCHAR(30) NOT NULL, 
my_date DATETIME NOT NULL,  
status VARCHAR(50), 
reg_date TIMESTAMP 
); 


INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('1', 'ABC', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('2', 'ABC', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('3', 'ABC', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('4', 'ABC', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('5', 'DEF', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('6', 'DEF', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('7', 'DEF', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('8', 'DEF', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('9', 'GHI', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('10', 'GHI', '2019/3/3', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('11', 'JKL', '2019/1/1', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('12', 'JKL', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('13', 'JKL', '2019/2/2', 'event', CURRENT_TIMESTAMP); 
INSERT INTO `mysql_test` (`id`, `company`, `my_date`, `status`, `reg_date`) VALUES ('14', 'JKL', '2019/1/1', 'event', CURRENT_TIMESTAMP); 

(SELECT company,
        SUM(MONTH(my_date) = 1) as Jan,
        SUM(MONTH(my_date) = 2) as Feb,
        SUM(MONTH(my_date) = 3) as Mar
 FROM mysql_test
 WHERE status = 'Event' AND my_date >= '2019-01-01' AND
       my_date < '2020-10-01'
 GROUP BY company
) UNION ALL
(SELECT 'Total',
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 1 THEN company END) as Jan,
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 2 THEN company END) as Feb,
        COUNT(DISTINCT CASE WHEN MONTH(my_date) = 3 THEN company END) as Mar
FROM mysql_test
WHERE status = 'Event' AND my_date >= '2019-01-01' AND
      my_date < '2020-10-01'
);

标签： mysqlpostgresql

解决方案

Postgres 不支持这种语法：

SUM(MONTH(my_date) = 1)

在 MySQL 中，条件返回0或1在数字上下文中评估时返回，但在 Postgres 中则不然。需要从布尔值显式转换为整数。此外，函数MONTH()不存在，您可以使用更标准的EXTRACT()，如下所示：

SUM( (EXTRACT(month from my_date) = 1)::int )

此外，最好将日期进行比较（即使我 Postgres 可能会为你做，如果你不这样做），所以这个：

my_date >= '2019-01-01'

可以改写：

my_date >= '2019-01-01'::date

这是您查询的新版本：

(
    SELECT company,
        SUM( (EXTRACT(month from my_date) = 1)::int ) as Jan,
        SUM( (EXTRACT(month from my_date) = 2)::int ) as Feb,
        SUM( (EXTRACT(month from my_date) = 3)::int ) as Mar
    FROM mysql_test
    WHERE 
        status = 'event' 
        AND my_date >= '2019-01-01'::date 
        AND my_date <  '2020-10-01'::date
 GROUP BY company
) UNION ALL (
    SELECT 
        'Total',
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 1 THEN company END) as Jan,
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 2 THEN company END) as Feb,
        COUNT(DISTINCT CASE WHEN EXTRACT(month from my_date) = 3 THEN company END) as Mar
    FROM mysql_test
    WHERE 
        status = 'event' 
        AND my_date >= '2019-01-01'::date 
        AND my_date < '2020-10-01'::date
)

DB Fiddle 上的演示：

公司| 一月 | 二月 | 马尔
:-------- | --: | --: | --:
美国广播公司 | 2 | 2 | 0
防御工事 | 0 | 2 | 2
全球健康指数 | 0 | 0 | 2
JKL | 2 | 2 | 0
总计 | 2 | 3 | 2

注意：在表的声明中也需要进行一些更改，请参见 db fiddle。

推荐阅读