MySQL查询可获取按月分组的最近12个月的数据，包括零计数 [英] MySQL query to get last 12 months data grouped by month including zero counts

问题描述

我有桌子orders (order_id, order_processed_date).我想要过去12个月每月的订单数.如果任何月份有0个订单，则应打印0.

I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.

类似这样的东西:

Total Orders | Month
-----------------------
2            | OCT 2018
3            | SEP 2018
0            | AUG 2018
0            | JUL 2018
1            | JUN 2018
0            | MAY 2018
0            | APR 2018
0            | MAR 2018
5            | FEB 2018
5            | JAN 2018
0            | DEC 2017
0            | NOV 2017

我已经在该网站中搜索并获得了一些查询，但没有找到确切的解决方案.我使用下面的查询得到结果，但是没有年份:

I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:

SELECT 
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month, 
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >= 
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub

可以请人帮我给我我想要的结果.

Can please someone help me to give me result as I required.

推荐答案

以下查询建立了前12个月的每月第一天的列表.例如，在Oct 19 2018上，列表将包含从Oct 01 2018到Nov 01 2017的日期.剩下的事由左联接完成:

The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:

SELECT date, COUNT(orders.primary_key)
FROM (
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date

这篇关于MySQL查询可获取按月分组的最近12个月的数据，包括零计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！