主页 / dba / 问题 / 291599
Accepted
ZedRed
Asked: 2021-05-15 12:42:49 +0800 CST

运行计算在子查询中不起作用，“不存在”

  • 772
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

我有一个查询，提供 2 个时间段内 2 个事物的计数：

  • 本周的时间段，一周的开始（周日/周一午夜）-> 今天
  • 上周的时间段，上周的开始（周日/周一午夜）->今天但上周

我还想要对返回的两个“汽车”计数整数执行计算，并将其与查询结果一起提供。

我当然可以在消费应用程序中执行此操作，但我希望能够在我的 SQL 中执行此操作。

工作 SQL

这是基本 SQL，也是我目前拥有和工作的：

WITH last_week AS (
  SELECT COUNT(car_name) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_name) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

设置数据库表

CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

导致错误的事情

我想添加以下代码 - 计算difference_between_weeks：

ROUND(car_count_current_week/(car_count_last_week/100) - 100)
    AS difference_between_weeks

我尝试在列中计算，我尝试添加另一个子查询。但我似乎无法计算difference_between_weeks它告诉我“不存在”或ERROR: division by zero

错误的完整 SQL

带有错误添加代码的 SQL 示例：

WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week,
  ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

返回错误

我收到以下错误：

ERROR:  division by zero
SQL state: 22012

我觉得我很亲密，但我也觉得这不可能？任何指向我所缺少的东西都将不胜感激。

postgresql postgresql-13

2 个回答

  1. 您的错误是 Postgres 将该列解释为 Integer 并执行欧几里得除法，这导致遇到零。

    将 car_count_last_week 更改为小数，将为您提供有理数除法

     (car_count_last_week::decimal/100)

    解决问题

    CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_date  TIMESTAMP,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

    INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');  

    WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  ,
  
 CASE WHEN car_count_last_week = 0 then 1
 ELSE ROUND(car_count_current_week/(car_count_last_week::decimal/100)) * 100 
 END 
   AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

    车名 | start_of_last_week | today_but_last_week | start_of_current_week | 今天| car_count_last_week | car_count_current_week | 周差
:------- | :----------------- | :----------------- | :--------------------- | :--------- | ------------------: | ---------------------: | ----------------------:
真棒| 2021-05-03 00:00:00 | 2021-05-09 00:00:00 | 2021-05-10 00:00:00+01 | 2021-05-15 | 1 | 4 | 40000

    db<>在这里摆弄

    • 2
  2. Best Answer

    我不明白为什么CROSS JOIN在这种情况下你需要 a ！他们是性能杀手！

    您可以通过执行以下操作大大简化此问题（并使其更普遍！） - 下面的所有代码都可以从这里的小提琴中获得：

    CREATE TABLE IF NOT EXISTS car_store 
(
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP(0) NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id),
     
     car_date DATE     GENERATED ALWAYS AS (car_time::DATE) STORED,
     car_week SMALLINT GENERATED ALWAYS AS (EXTRACT(WEEK FROM car_time)) STORED
);

    请注意GENERATED（又名“计算”或“虚拟”字段）的使用。PG 12 介绍了这些，既然你正在运行 13，我们就是黄金！

    PG 的实现目前只有STORED存储类型，但是当VIRTUAL类型出现时，在这种情况下可能会更好——尽管字段只有 6 个字节（4 个字节DATE和 2个字节SMALLINT）。

    我还添加了数据——因为 6 条记录并不能真正为任何测试提供良好的基础！

    样本：

    -- Week starting on Mon (19-04) and ending on Sunday (25-04) (poor sales)


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:01:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:02:27'); 


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-20 12:12:00');

-- no sales on Tuesday

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:05:27');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:06:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:07:27'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:08:00');
..
..  ~ 60 more records snipped...
..

    我当时做的是：

    SELECT 
  cwk,
  c_name,
  LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid'
  END AS status

FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
ORDER BY cwk DESC, c_name;

    结果：

    cwk c_name  last_wk_sales   this_week_sales   status
19  awesome            16                17   Figures week to date
18  awesome            22                16   Figures valid
17  awesome            12                22   Figures valid
16  awesome     NULL                     12   *** No previous sales for period***

    请注意 LAG() PostgreSQL WINDOW 函数的使用 - 这些功能非常强大，非常值得了解 - 它们将回报您多次学习它们所花费的时间和精力...... LAG() 非常适合您希望的用例将上周的销售额与本周的销售额进行比较！

    然后我添加了百分比的计算：

    SELECT 
  cwk,
  c_name,
  LAG(wk_cnt) OVER w AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt) OVER w IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid - period closed'
  END AS status,
  wk_cnt - LAG(wk_cnt) OVER w AS diff,
  ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change
FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
ORDER BY cwk DESC, c_name;

    结果（未格式化，最后两（新）列除外）：

    cwk c_name  last_wk_sales   this_week_sales status      diff     pc_change
19  awesome 16   17 Figures week to date                   1             6
18  awesome 22   16 Figures valid - period closed         -6           -27
17  awesome 12   22 Figures valid - period closed         10            83
16  awesome NULL 12 *** No previous sales for period***

    所以，我们有绝对数字的差异和与前一周相比的百分比变化

    我还添加了这个：

    WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)

    这是整理 SQL 的另一种方法——你不必把那个繁琐WINDOW的定义到处乱写——只是一点糖，但很高兴拥有！

    我还解决了@nbk 在 INTEGER 部门中指出的问题 - 是的，它可能会绊倒人们，但只要了解这个问题并继续前进！

      ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change

    我使用了REAL类型而不是DECIMAL- 从这里开始，它只有 4 个字节并且精确到小数点后 6 位 - 我几乎不认为这个用例需要更多。此外，从该页面：

    但是，与整数类型或下一节中描述的浮点类型相比，数值的计算非常慢。

    您可以通过将批次放在子查询中来摆脱繁琐的CASE WHEN LAG(wk_cnt) OVER w IS NULL位，如下所示：

    SELECT 
  *, 
  (TO_DATE(cyr::TEXT || cwk::TEXT, 'YYYYWW') + INTERVAL '3 DAY')::DATE AS "Week starting:"
FROM
(
  SELECT 
...
... body of query snipped - see fiddle - it is the same as above
...
  ) AS tab
  WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
) AS tab2
WHERE last_wk_sales IS NOT NULL -- could also add dates between...
ORDER BY cwk DESC, c_name;

    你甚至可能不需要这个 - 如果你总是每周至少卖出一辆车 - 否则，你可以使用此处JOIN所示的日历表。

    子查询的性能似乎并没有受到太大影响（参见小提琴），尽管我会敦促您使用自己的数据和硬件设置测试各种索引 - 尽管对于汽车销售，记录数不是t 可能很大。

    • 2

相关问题