Postgresql查询在一年内获得每个月的计数 [英] Postgresql query to get count per months within one year

问题描述

在MySQL中

  SELECT y，m，Count（users.created_date）
 FROM（
 SELECT y，m 
 FROM 
（SELECT YEAR（CURDATE（））y UNION ALL SELECT YEAR（CURDATE（）） -  1）years，
 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 
 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12）months）ym 
 LEFT JOIN users 
 ON ym.y = YEAR（FROM_UNIXTIME（users.created_date））
 AND ym.m = MONTH（FROM_UNIXTIME（users.created_date）） 
 WHERE 
（y = YEAR（CURDATE（））AND m <= MONTH（CURDATE（））
 OR 
（y< YEAR（CURDATE（））AND m& MONTH（CURDATE（）））
 GROUP BY y，m; 
  

Mysql输出

  + ------ + ---- + ---------------------- + 
 | y | m |计数（users.created）| 
 + ------ + ---- + ---------------------- + 
 | 2012 | 5 | 5595 | 
 | 2012 | 6 | 4431 | 
 | 2012 | 7 | 3299 | 
 | 2012 | 8 | 429 | 
 | 2012 | 9 | 0 | 
 | 2012 | 10 | 3698 | 
 | 2012 | 11 | 6208 | 
 | 2012 | 12 | 5142 | 
 | 2013 | 1 | 1196 | 
 | 2013 | 2 | 10 | 
 | 2013 | 3 | 0 | 
 | 2013 | 4 | 0 | 
 + ------ + ---- + ---------------------- + 
  
 
 
 
 ** IN POSTGRESQL QUERY ** 
  SELECT to_char（created_date，'MM'），
 count（id）
 FROM users 
 WHERE created_date> 
 date_trunc（'month'，CURRENT_DATE） -  INTERVAL'1 year'
 GROUP BY 1 
  
 
 $ b b 
  POSTGRESQL OUTPUT  
  postgresql输出 
 
 
 
我可以知道如何获得postgresql输出像mysql输出。需要在一年内获取当前月份的结果数据。
解决方案
我假设你正在寻找的是行其中count为0.如果是这种情况，您可以使用generate_series和表上的左连接数据：
  SELECT to_char （'，''''）''''''''''''''''''''''''''''''''''''''''''''''''''; ）as i 
 left join users on（to_char（i，'YY'）= to_char（created_date，'YY'）
 and to_char（i，'MM'）= to_char（created_date，'MM' ）and login_type = 1）
 GROUP BY 1,2; 
  
 
In MySQL    
SELECT y, m, Count(users.created_date)
     FROM (
      SELECT y, m
      FROM
         (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
         (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
           UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
           UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
       LEFT JOIN users
       ON ym.y = YEAR(FROM_UNIXTIME(users.created_date))
          AND ym.m = MONTH(FROM_UNIXTIME(users.created_date))
     WHERE
       (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
       OR
       (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
     GROUP BY y, m;
Mysql Output
+------+----+----------------------+
| y    | m  | Count(users.created) |
+------+----+----------------------+
| 2012 |  5 |                 5595 |
| 2012 |  6 |                 4431 |
| 2012 |  7 |                 3299 |
| 2012 |  8 |                  429 |
| 2012 |  9 |                    0 |
| 2012 | 10 |                 3698 |
| 2012 | 11 |                 6208 |
| 2012 | 12 |                 5142 |
| 2013 |  1 |                 1196 |
| 2013 |  2 |                   10 |
| 2013 |  3 |                    0 |
| 2013 |  4 |                    0 |
+------+----+----------------------+
** IN POSTGRESQL QUERY**
SELECT to_char(created_date, 'MM'),
       count(id)
FROM users
WHERE created_date >
      date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY 1
POSTGRESQL OUTPUT
postgresql output

May I know how to get postgresql output as like mysql output. Need to get results for current month within one year data.
解决方案
I'm assuming what you're looking for is the rows where count is 0. If that is the case you can use generate_series and a left join on your table with data:
    SELECT to_char(i, 'YY'), to_char(i, 'MM'),
        count(id)
    FROM generate_series(now() - INTERVAL '1 year', now(), '1 month') as i
    left join users on (to_char(i, 'YY') = to_char(created_date, 'YY') 
                        and to_char(i, 'MM') = to_char(created_date, 'MM') and login_type = 1)
    GROUP BY 1,2;


                        
这篇关于Postgresql查询在一年内获得每个月的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！