sql - 从具有创建日期的表中获取上周的数据

Question

使用以下查询检索上周数据，但出现错误

Postgres ERROR: syntax error at or near "CAST" Position: 127

不知道哪里出错了:

SELECT count(*), extract(day from createdon) AS period
FROM orders
WHERE servicename =:serviceName AND  createdon BETWEEN 
    CAST(NOW() AS CAST(DATE-EXTRACT(DOW FROM NOW()) AS INTEGER-7)) AND
    CAST(NOW() AS CAST(DATE-EXTRACT(DOW from NOW()) AS INTEGER))
GROUP BY  extract(day from createdon)
ORDER BY extract(day from createdon);

Answer 1

你把事情搞得太复杂了。要获取上周的数据，只需获取“本周开始”减去 7 天后的所有内容即可:

可以使用 date_trunc('week', current_date) 评估“本周开始” .

如果减去 7 天，您将得到前一周的开始:date_trunc('week', current_date) - interval '7' day .如果减去 1 天，您将得到前一周的结束。

date_trunc总是使用星期一作为一周的开始，所以如果你的一周从星期日开始，只需再减去一个，例如date_trunc('week', current_date)::date - 8将是前一周的星期日

将所有这些放在一起你会得到:

SELECT count(*), extract(day from createdon) AS period
FROM orders
WHERE servicename =:serviceName 
  AND createdon 
      between date_trunc('week', current_date)::date - 7 
          and date_trunc('week', current_date)::date - 1
GROUP BY extract(day from createdon)
ORDER BY extract(day from createdon);

如果您的列是时间戳列，您可以简单地转换 createdon删除时间部分的日期:

  AND createdon::date 
      between date_trunc('week', current_date)::date - 7 
          and date_trunc('week', current_date)::date

请注意 createdon 上的常规索引不会用于该条件，您需要在 createdon::date 上创建索引如果您需要性能。

如果你不能(或不想)创建这样的索引，你需要使用不同的东西 between

  AND createdon >= date_trunc('week', current_date)::date - 7 
  AND createdon < date_trunc('week', current_date)::date

(注意使用 < 而不是 <= `between 正在使用)

另一种选择是将日期信息转换为周和年的组合:

AND to_char(createdon, 'iyyy-iw') = to_char(date_trunc('week', current_date)::date - 7, 'iyyy-iw')

请注意，我使用了 ISO week definition对于上述。如果您使用不同的周编号系统，则需要不同的 format mask对于 to_char()功能。