简化SQL查询

Question

我正在使用 https://www.bixi.com/en/开放数据并被要求找到“数据集中每年一年组合的平均旅行数量”。 表

一个	是	的	：	我	示例	这
的	要	查询	​	​	​	​
​	​15 17:16:00	6315	2016-04-15 17:37:00	6315	1293	0

我过去成功回答问题的查询如下，但是我该如何简化它？

SELECT
    daily_trips_2016.avg_daily_trips AS avg_daily_trips_2016,
    daily_trips_2017.avg_daily_trips AS avg_daily_trips_2017,
    daily_trips_2016.month
FROM
    (
    SELECT -- This sub-query determines the average number of daily trips per month for the year 2016
        ROUND(COUNT(*) / (COUNT(DISTINCT DAY(start_date))), 0) AS avg_daily_trips,
        MONTH(start_date) AS month
    FROM trips
    WHERE YEAR(start_date) = 2016
    GROUP BY month) AS daily_trips_2016
LEFT JOIN
    (
    SELECT -- This sub-query determines the average number of daily trips per month for the year 2017
        ROUND(COUNT(*) / (COUNT(DISTINCT DAY(start_date))), 0) AS avg_daily_trips,
        MONTH(start_date) AS month
    FROM trips
    WHERE YEAR(start_date) = 2017
    GROUP BY month) AS daily_trips_2017
ON daily_trips_2016.month = daily_trips_2017.month; -- Since both sub-queries share the "month" column, the JOIN happens on this column
-- and we can see the daily averages side by side for each year

Answer 1

使用条件聚合：

SELECT ROUND(SUM(YEAR(start_date) = 2016) / COUNT(DISTINCT CASE WHEN YEAR(start_date) = 2016 THEN DAY(start_date) END), 0)  AS avg_daily_trips_2016,
       ROUND(SUM(YEAR(start_date) = 2017) / COUNT(DISTINCT CASE WHEN YEAR(start_date) = 2017 THEN DAY(start_date) END), 0)  AS avg_daily_trips_2017,
       MONTH(start_date) AS month
FROM trips
WHERE YEAR(start_date) IN (2016, 2017)
GROUP BY month;

Answer 2

查找“每年平均每年旅行的平均数量
数据集中的组合”。

SELECT ALL
    EXTRACT(YEAR_MONTH FROM start_date) AS period,
    COUNT(*) / COUNT(DISTINCT DATE(start_date)) AS avg_by_day
FROM trips
GROUP BY 1
ORDER BY 1 ASC

。Yyyymm /平均值1行