主页 / user-229891

ZedRed's questions

Martin Hope
ZedRed
Asked: 2021-05-15 12:42:49 +0800 CST
  • 2
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