sql按季节分组

Question

我在SQL中使用'group by‘和’select-case‘有问题。

我有一张桌子:促销(名字，CodProd，CodProdSimilar，StartDate，EndDate，折扣)，我需要做的是:知道什么季节是促销活动。

例如：

<table style="width:100%">
  <tr>
    <td>Name</td>
    <td>StartDate(dd/mm/yyyy)</td> 
    <td>EndDate(dd/mm/yyyy)</td>
    <td>Season (southern hemisphere)</td>
  </tr>
  <tr>
    <td>Prom1 </td>
    <td>02/01/2015 </td> 
    <td>09/01/2015</td>
    <td>Summer</td>
  </tr>
    <tr>
    <td>Prom2 </td>
    <td>02/01/2015 </td> 
    <td>09/04/2015</td>
    <td>Summer</td>
  </tr>
    <tr>
    <td>Prom2 </td>
    <td>02/01/2015 </td> 
    <td>09/04/2015</td>
    <td>Autumn</td>
  </tr>
</table>

这是很容易解决的，当一个促销只在一个季节，但我还不能这样做的情况下，Prom2。

我的代码：

select 
    Name, StartDate, EndDate,
    CASE
       WHEN EXTRACT(MONTH from StartDate) < 3 THEN 'Summer'
       WHEN EXTRACT(MONTH from StartDate) = 3 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Summer' 
               ELSE 'Autumn' 
            END
       WHEN EXTRACT(MONTH from StartDate) < 6 THEN 'Autumn'
       WHEN EXTRACT(MONTH from StartDate) = 6 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Autumn' 
               ELSE 'Winter' 
            END
       WHEN EXTRACT(MONTH from StartDate) < 9 THEN 'Winter'
       WHEN EXTRACT(MONTH from StartDate) = 9 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Winter' 
               ELSE 'Spring' 
            END
       WHEN EXTRACT(MONTH from StartDate)< 12 THEN 'Spring'
       WHEN EXTRACT(MONTH from StartDate) = 12 THEN
            CASE 
               WHEN EXTRACT(DAY from StartDate) <= 20 THEN 'Spring' 
               ELSE 'Summer' 
            END
    END as season
from 
    promotion

有什么想法吗？

非常感谢您的时间！

Answer 1

对于您的情况，您可以将列转换为字符串，使用我们可以确定季节的月份和日期。

select Name,StartDate,EndDate,
case 
            when to_char(StartDate,'MMDD') between '0321' and '0620' then 'spring'
            when to_char(StartDate,'MMDD') between '0621' and '0922' then 'summer'
            when to_char(StartDate,'MMDD') between '0923' and '1220' then 'fall'
            else                                                        'winter'
        end as season
FROM promotion

请参考这个，你会得到更多的信息。

dimensions

PostgreSQL group by season (northern and southern hemisphere)