SQL-根据第二列的排名，将列中的项目彼此相减，并按第三列聚合

Question

CREATE TABLE table_1 (
`userid` VARCHAR(2),
`date_accessed` DATE,
`rank` INT,
`country` VARCHAR(2)
);
INSERT INTO table_1
(`userid`, `date_accessed`, `rank`, `country`)
VALUES
('A.', '2019-01-01', 1, 'US'),
('B.', '2019-01-02', 1, 'FR'),
('A.', '2019-01-03', 2, 'US'),
('A.', '2019-01-04', 3, 'US'),
('B.', '2019-01-04', 2, 'FR'); 

Fiddle：https://www.db-fiddle.com/f/caFeo5c4Try1q7FawHN7MC/1

我的数据集如下：

user | date      | rank | state
A.   |2020-01-01 | 1    | NY
A.   |2020-01-04 | 2    | NY
A.   |2020-01-08 | 3    | NY
B.   |2020-01-01 | 1    | CA
B.   |2020-01-02 | 2    | CA
B.   |2020-01-04 | 3    | CA

这是一堆WITH intermediate_table AS语句的产物，但在我的查询中，这是我正在使用的结构。

对于每个用户，我想用排名2的日期减去排名1的日期。因此，对于用户A，我希望获得介于1/1和1/4之间的DATEDIFF。理想情况下，输出如下所示：

user | date_diff  | state
A.   | 3          | NY
B.   | 1          | CA

最终目标是获得每个国家的平均date_diff，即该国家所有用户的平均值。

Answer 1

对于此示例数据，一种方法是GROUP BY user, state并使用条件聚合来减去日期：

SELECT 
user, 
DATEDIFF(
MAX(CASE WHEN `rank` = 2 THEN date END),
MAX(CASE WHEN `rank` = 1 THEN date END)
) date_diff,
state
FROM tablename
WHERE `rank` IN (1, 2)
GROUP BY user, state

请参阅演示
结果：

> user | date_diff | state
> :--- | --------: | :----
> A.   |         3 | NY   
> B.   |         1 | CA