1127. User Purchase Platform----分组统计，通过产生新的join table来统计不存在的字段组合

Table: Spending

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| spend_date  | date    |
| platform    | enum    | 
| amount      | int     |
+-------------+---------+
The table logs the spendings history of users that make purchases from an online shopping website which has a desktop and a mobile application.
(user_id, spend_date, platform) is the primary key of this table.
The platform column is an ENUM type of ('desktop', 'mobile').

Write an SQL query to find the total number of users and the total amount spent using mobile only, desktop only and both mobile and desktop together for each date.

The query result format is in the following example:

Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1       | 2019-07-01 | mobile   | 100    |
| 1       | 2019-07-01 | desktop  | 100    |
| 2       | 2019-07-01 | mobile   | 100    |
| 2       | 2019-07-02 | mobile   | 100    |
| 3       | 2019-07-01 | desktop  | 100    |
| 3       | 2019-07-02 | desktop  | 100    |
+---------+------------+----------+--------+

Result table:
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop  | 100          | 1           |
| 2019-07-01 | mobile   | 100          | 1           |
| 2019-07-01 | both     | 200          | 1           |
| 2019-07-02 | desktop  | 100          | 1           |
| 2019-07-02 | mobile   | 100          | 1           |
| 2019-07-02 | both     | 0            | 0           |
+------------+----------+--------------+-------------+ 
On 2019-07-01, user 1 purchased using both desktop and mobile, user 2 purchased using mobile only and user 3 purchased using desktop only.
On 2019-07-02, user 2 purchased using mobile only, user 3 purchased using desktop only and no one purchased using both platforms.

解题思路：

1，难点一：如何产生一行，显示该交易日交易额为0，那么再使用聚合统计之前，该列的值应该是null，那么需要一个包含就交易日和platform所有可能组合的表，进行join。包含所有platform的表如下：

SELECT DISTINCT platform FROM spending
UNION
SELECT 'both'

包含所有日期的表如下：

SELECT DISTINCT spend_date
FROM spending

两者组合：

with tmp2 AS(
SELECT DISTINCT platform FROM spending
UNION
SELECT 'both'),
tmp3 AS (
SELECT DISTINCT spend_date
FROM spending)
SELECT *
FROM tmp2, tmp3
WHERE 1=1

得到结果：

2、统计每个交易日不同平台的交易额

SELECT DISTINCT user_id, spend_date,
CASE WHEN COUNT(platform) OVER (PARTITION BY spend_date, user_id) = 2 
THEN 'both' ELSE platform END AS platform, 
/*当同一个id同一天交易平台数量为2时，说明其使用了both platform，否则只用了一个交易平台*/
SUM(amount) OVER (PARTITION BY spend_date, user_id) AS total_amount
/*对同一个id同一天的交易额进行统计*/
FROM spending

3、上下结果left join，使用ifnull()函数进行判断，最后得到所需要的结果。这里，count（字段）如果统计到null，则返回0.

WITH tmp1 AS
(SELECT DISTINCT user_id, spend_date,
CASE WHEN COUNT(platform) OVER (PARTITION BY spend_date, user_id) = 2 
THEN 'both' ELSE platform END AS platform, 
SUM(amount) OVER (PARTITION BY spend_date, user_id) AS total_amount
FROM spending),
tmp2 AS(
SELECT DISTINCT platform FROM spending
UNION
SELECT 'both'),
tmp3 AS (
SELECT DISTINCT spend_date
FROM spending),
tmp AS (
SELECT *
FROM tmp2, tmp3
WHERE 1=1)
SELECT tmp.spend_date, tmp.platform, IFNULL(SUM(total_amount),0) as total_amount, COUNT(user_id) as total_users
FROM tmp1 RIGHT JOIN tmp
ON tmp1.spend_date = tmp.spend_date
AND tmp1.platform = tmp.platform
GROUP BY tmp.spend_date, tmp.platform