MySQL:计算两个日期列并按天分组

Question

我需要绘制一个折线图，以可视化特定日期之间每天的订单和取货情况。订单和提货日期存储在 unixtime 中。我的 table 看起来像这样:

id    order_date    pickup_date
-------------------------------
1     1472749664     1472133376
2     1472551372     1472567548
3     1472652545     1472901368
4     1473154659     1473512323
5     1473512923     1475229824
6     1475586643     1475652635

我追求的是这样的

date        orders    pickups
-------------------------------
01-09-2016  1         0
02-09-2016  4         1
03-09-2016  3         2
04-09-2016  7         1
05-09-2016  0         0
06-09-2016  1         1
07-09-2016  6         3
08-09-2016  0         0
08-09-2016  3         5
10-09-2016  2         4

我知道我可以根据一列进行计数，例如:

SELECT
    COUNT(id) AS orders,
    FROM_UNIXTIME(order_dates, '%d-%m-%Y') AS date
FROM orders
GROUP BY request_date

但我不知道如何计算两列并将它们每天分组。

Answer 1

您可以使用如下查询:

SELECT sum(orders) as orders, sum(pickups) as pickups, date
FROM (
SELECT
    COUNT(id) AS orders, 0 as pickups,
    FROM_UNIXTIME(`order_date`, '%d-%m-%Y') AS date
FROM orders
GROUP BY order_date
UNION
SELECT
    0 AS orders, COUNT(id) as pickups,
    FROM_UNIXTIME(`pickup_date`, '%d-%m-%Y') AS date
FROM orders
GROUP BY pickup_date ) ut
GROUP BY date

Here是一把 fiddle 。