在postgresql中按月和年分组查询结果

6 回答

• 1

  select to_char(date,'Mon') as mon,
         extract(year from date) as yyyy,
         sum("Sales") as "Sales"
  from yourtable
  group by 1,2
  

  应Radu的要求，我将解释该查询：

  to_char(date,'Mon') as mon, ：将"date"属性转换为月份缩写形式的已定义格式 .

  extract(year from date) as yyyy ：Postgresql的"extract"函数用于从"date"属性中提取YYYY年份 .

  sum("Sales") as "Sales" ：SUM（）函数将所有"Sales"值相加，并提供区分大小写的别名，并使用双引号保持区分大小写 .

  group by 1,2 ：GROUP BY函数必须包含SELECT列表中不属于聚合的所有列（也就是说，所有列都不在SUM / AVG / MIN / MAX等函数内） . 这告诉查询应该为每个唯一的列组合应用SUM（），在这种情况下，列是月和年列 . "1,2"部分是简写而不是使用列别名，尽管最好使用完整的"to_char(...)"和"extract(...)"表达式来提高可读性 .

  回复于 2024-05-22T06:59:48+08:00
• 1

  我无法相信接受的答案有这么多的赞成 - 这是一种可怕的方法 .

  这是正确的方法，使用date_trunc：

  SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
       FROM yourtable
   GROUP BY txn_month
  

  这是不好的做法，但如果你使用，你可能会被原谅

  GROUP BY 1
  

  在一个非常简单的查询中 .

  你也可以使用

  GROUP BY date_trunc('month', txn_date)
  

  如果您不想选择日期 .

  回复于 2024-05-22T06:59:48+08:00
• 149

  to_char 实际上让你一举拔出年份和月份！

  select to_char(date('2014-05-10'),'Mon-YY') as year_month; --'May-14'
  select to_char(date('2014-05-10'),'YYYY-MM') as year_month; --'2014-05'
  

  或者在上述用户示例的情况下：

  select to_char(date,'YY-Mon') as year_month
         sum("Sales") as "Sales"
  from some_table
  group by 1;
  

  回复于 2024-05-22T06:59:48+08:00
• 23

  bma 答案很棒！我已将它与ActiveRecords一起使用，如果有人在Rails中需要它：

  Model.find_by_sql(
    "SELECT TO_CHAR(created_at, 'Mon') AS month,
     EXTRACT(year from created_at) as year,
     SUM(desired_value) as desired_value
     FROM desired_table
     GROUP BY 1,2
     ORDER BY 1,2"
  )
  

  回复于 2024-05-22T06:59:48+08:00
• 187

  还有另一种方法可以使用postgres中的date_part（）函数来实现结果 .

  SELECT date_part('month', txn_date) AS txn_month, date_part('year', txn_date) AS txn_year, sum(amount) as monthly_sum
       FROM yourtable
   GROUP BY date_part('month', txn_date)
  

  谢谢

  回复于 2024-05-22T06:59:48+08:00
• 0

  Postgres有几种类型的时间戳：

  timestamp without timezone - （最好存储UTC时间戳）您可以在多国数据库存储中找到它 . 在这种情况下，客户将负责每个国家/地区的时区偏移 .

  timestamp with timezone - 时区偏移量已包含在时间戳中 .

  在某些情况下，您的数据库不使用时区，但仍需要根据本地时区和夏令时对记录进行分组（例如https://www.timeanddate.com/time/zone/romania/bucharest）

  要添加时区，您可以使用此示例并将时区偏移替换为您的时区 .

  "your_date_column" at time zone '+03'
  

  要添加特定于夏令时的1夏令时偏移，您需要检查您的时间戳是否属于夏令时 . 由于这些间隔在1或2天内变化，我将使用不影响月末记录的aproximation，因此在这种情况下我可以忽略每年的确切间隔 .

  如果必须构建更精确的查询，则必须添加条件以创建更多案例 . 但粗略地说，当您在数据库中找到没有时区的时间戳时，这将在 splitting data per month in respect with timezone and SummerTime 中正常工作：

  SELECT 
      "id", "Product", "Sale",
      date_trunc('month', 
          CASE WHEN 
              Extract(month from t."date") > 03 AND
              Extract(day from t."date") > 26 AND
              Extract(hour from t."date") > 3 AND
              Extract(month from t."date") < 10 AND
              Extract(day from t."date") < 29 AND
              Extract(hour from t."date") < 4
          THEN 
              t."date" at time zone '+03' -- Romania TimeZone offset + DST
          ELSE
              t."date" at time zone '+02' -- Romania TimeZone offset 
          END) as "date"
  FROM 
      public."Table" AS t
  WHERE 1=1
      AND t."date" >= '01/07/2015 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
      AND t."date" < '01/07/2017 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
  GROUP BY date_trunc('month', 
      CASE WHEN 
          Extract(month from t."date") > 03 AND
          Extract(day from t."date") > 26 AND
          Extract(hour from t."date") > 3 AND
          Extract(month from t."date") < 10 AND
          Extract(day from t."date") < 29 AND
          Extract(hour from t."date") < 4
      THEN 
          t."date" at time zone '+03' -- Romania TimeZone offset + DST
      ELSE
          t."date" at time zone '+02' -- Romania TimeZone offset 
      END)
  

  回复于 2024-05-22T06:59:48+08:00