时间戳

首页 > 解决方案 > 查询几个不连续范围的最后一个

sql - 查询几个不连续范围的最后一个

问题描述

我有一个气象站读数的 postgresql 表：

  CREATE TABLE "weather"
    (
    "id" SERIAL PRIMARY KEY,
    "dtstamp" TIMESTAMP NOT NULL UNIQUE,
    "temperature" REAL NOT NULL,
    ...
    )

对于园艺，我对第一次和最后一次霜冻的日期很感兴趣。

我可以得到2019年春天的最后一次霜冻：

  SELECT dtstamp FROM weather WHERE temperature<0
    AND dtstamp BETWEEN '2019-01-01' AND '2019-07-01'
    ORDER BY dtstamp DESC LIMIT 1
  2019-04-13 00:55:16

还有2019年的初霜：

  SELECT dtstamp FROM weather WHERE temperature<0
    AND dtstamp BETWEEN '2019-07-01' AND '2019-12-31'
    ORDER BY dtstamp ASC LIMIT 1
  2019-10-28 03:59:20

其他年份也是如此。我如何在几年内查询这个表？

标签： sqlpostgresql

解决方案

您可以提取月份进行过滤和聚合。例如：

SELECT EXTRACT(year FROM dtstamp),
       MAX(dtstamp) FILTER (WHERE temperature < 0)
FROM weather
WHERE temperature < 0 AND
      EXTRACT(month FROM dtstamp) < 7
GROUP BY EXTRACT(year FROM dtstamp);

和：

SELECT EXTRACT(year FROM dtstamp),
       MIN(dtstamp) FILTER (WHERE temperature < 0)
FROM weather
WHERE temperature < 0 AND
      EXTRACT(month FROM dtstamp) >= 7
GROUP BY EXTRACT(year FROM dtstamp);

您也可以将其放在一个查询中：

SELECT EXTRACT(year FROM dtstamp),
       MAX(dtstamp) FILTER (WHERE temperature < 0 AND EXTRACT(month FROM dtstamp) < 7) as latest_spring_frost,
       MIN(dtstamp) FILTER (WHERE temperature < 0 AND EXTRACT(month FROM dtstamp) >= 7) as earliest_autumn_frost
FROM weather
WHERE temperature < 0          
GROUP BY EXTRACT(year FROM dtstamp);

推荐阅读