PostgreSQL集团季节（北半球和南半球） [英] PostgreSQL group by season (northern and southern hemisphere)

问题描述

我有遍布全球的数据点。每个数据点都有一个时间戳。

我想按季节对数据进行分组，如图所示（来源：

这是我试过的到目前为止（冬季2011/2012在北半球）：

  SELECT * FROM my_table 
 WHERE my_date BETWEEN'2011 -12-21'AND'2012-03-21'
 AND ST_Y（ST_Centroid（geom））> 0; 
  

如何在所有可能的年份这样做？我试过...或者日期BETWEEN ...但这是非常慢的。

如何在北半球和南半球选择冬天？

更新

对于北半球的所有冬季数据，我使用此查询： （








$ ）:: int AS month，
extract（day from my_date）:: int AS day，
ST_Centroid（geom）AS geom
FROM mytable
WHERE ST_Y（ST_Centroid（geom） ）> 0）

（SELECT * FROM first_step
WHERE month = 12
AND day> = 21）

UNION ALL

（SELECT * FROM first_step
WHERE month = ANY（'{1,2}'））

UNION ALL

（SELECT * FROM first_step
WHERE month = 3
AND day< 21）

有没有更优雅或更有效的解决方案？

解决方案

  with t（my_date）as（
 values（'2012-07-01':: date），（'2013-03 -30'），（'2013-10-12'），（'2013-01-10'）
）
选择
 case 
当my_date在
之间make_date（extract（year from my_date）:: int，3，21）
和
 make_date（extract（year from my_date）:: int，6，20）
 then'spring'
当my_date在
 make_date（extract（year from my_date）:: int，6，21）
和
 make_date（extract（year from my_date）:: int，9，22 ）
 then'summer'
当my_date在
 make_date（extract（year from my_date）:: int，9,23）
和
 make_date（extract（year从my_date）:: int，12，20）
然后'fall'
 else'winter'
结束为季节，
 my_date 
从t 
 ; 
 season | my_date 
 -------- + ------------ 
 summer | 2012-07-01 
 spring | 2013-03-30 
 fall | 2013-10-12 
 winter | 2013-01-10 
  

I have data points distributed over the whole globe. Every data point has a timestamp.

I want to group the data by the seasons, as shown in this picture (source: https://en.wikipedia.org/wiki/Season#/media/File:Seasons1.svg).

This is what I tried so far (winter 2011/2012 in the northern hemisphere):

SELECT * FROM my_table 
    WHERE my_date BETWEEN '2011-12-21' AND '2012-03-21'
    AND ST_Y(ST_Centroid(geom)) > 0;

How can I do this for all possible years? I tried ...OR date BETWEEN... but this is very slow.

How is it possible to select both winter in the northern and southern hemisphere?

Update

For all winter data on the northern hemisphere I use this query:

CREATE TABLE season_winter_north AS WITH first_step AS (
SELECT
    id,
    extract(month from my_date)::int AS month,
    extract(day from my_date)::int AS day,
    ST_Centroid(geom) AS geom
FROM mytable 
    WHERE ST_Y(ST_Centroid(geom)) > 0)

(SELECT * FROM first_step
    WHERE month = 12
        AND day >= 21)

UNION ALL

(SELECT * FROM first_step
    WHERE month = ANY('{1,2}'))

UNION ALL

(SELECT * FROM first_step
    WHERE month = 3
        AND day<21)

Is there a more elegant or efficient solution?

解决方案

with t(my_date) as (
    values ('2012-07-01'::date), ('2013-03-30'), ('2013-10-12'), ('2013-01-10')
)
select 
    case 
        when my_date between 
            make_date(extract(year from my_date)::int, 3, 21)
            and
            make_date(extract(year from my_date)::int, 6, 20)
        then 'spring'
        when my_date between 
            make_date(extract(year from my_date)::int, 6, 21)
            and
            make_date(extract(year from my_date)::int, 9, 22)
        then 'summer'
        when my_date between 
            make_date(extract(year from my_date)::int, 9, 23)
            and
            make_date(extract(year from my_date)::int, 12, 20)
        then 'fall'
        else 'winter'
    end as season,
    my_date
from t
;
season |  my_date   
--------+------------
summer | 2012-07-01
spring | 2013-03-30
fall   | 2013-10-12
winter | 2013-01-10

这篇关于PostgreSQL集团季节（北半球和南半球）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！