Mysql – Select all customers and number of orders per month as csv

mariadbMySQL

This is what I have:

customer
--------
customerId INT(10)
name TINYTEXT
phone VARCHAR(10)
...

order
-----
orderId int(10)
customerId INT(10)
dateCompleted DATETIME
...

This is what I need:

custId  name  thisMo  lastMo  last3Mos  monthsThisYear              ordersPerMonth
------  ----  ------  ------  --------  --------------              --------------
1       John  5       10        28      2019-01,2019-02,2019-03,..  7,11,9..
2       Bill  9       15        33      2019-01,2019-02,2019-03,..  12,10,14..
3       Jack  3       8         19      2019-01,2019-02,2019-03,..  3,7,5..

Trying to generate a table showing customers, number of orders they've placed this month, last month, and last 3 months, along with a spark line chart showing number of orders per month for the entire past year (hence the comma separated lists of months and orders per month)

Currently, they're using a single query to get all customers, then each customer is looped through to generate the table and additional queries are made to get the numbers. The queries to get the csv are as follows:

SELECT GROUP_CONCAT(`count` SEPARATOR ',') AS `countCsv`
FROM (
    SELECT SUM(IF(`customerId` = {$row['customerId']}, 1, 0)) AS `count`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

SELECT GROUP_CONCAT(`month` SEPARATOR ',') AS `monthCsv`
FROM (
    SELECT DATE_FORMAT(`dateCompleted`, '%Y-%m') AS `month`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

…but this is resulting in 5 extra queries per row per page load and it's taking forever. I've been trying all day (8-1/2 hours now) to search and extrapolate from similar questions and I just can't get anything even close to working in a single query.

Edit – added customerId to order table to show customerId as the relationship between the two tables.

Best Answer

Step 1: Get the data without the complexity:

CREATE TEMPORARY TABLE t
SELECT cus.customerId,
       cus.name,
       CONCAT(cus.LEFT(dateCompleted, 7), '-01') AS month_of,
       o.orderId
    FROM Customer AS cus
    JOIN orders AS o  ON ??? -- how do the table relate??
    ;

(Fox and try that query now.)

Step 2: Compute the counts:

SELECT customerId, COUNT(*) AS ct
    FROM t
    WHERE dateCompleted
    GROUP BY customerId, month_of
    ;

(Try that query now.)

Step 3: Use that as a "derived" table to build the ultimate table.

SELECT a.customerId AS custId,
       a.name,
       SUM(a.month_of >= CURDATE() - INTERVAL 1 MONTH) AS thisMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 2 MONTH AND
           a.month_of  < CURDATE() - INTERVAL 2 MONTH) AS lastMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 3 MONTH) AS last3mo,
       GROUP_CONCAT( ??? )  as monthsThisYear,  -- unclear where this comes from
       GROUP_CONCAT(t.ct ORDER BY month_of) AS ordersPerMonth
    FROM ( ..query 1, above.. ) AS a
    JOIN ( ..query 2, above.. ) AS cts  USING(customerId)
    GROUP BY customerId, a.name

There are several holes, but maybe this gives you some useful clues.

If you are using MySQL 8.0 or MariaDB 10.2, use WITH instead of a temp table.

Related Question