sql-根据第2列中的排名和第3列的聚合,将列中的项相互减去

jgzswidk  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(361)
  1. CREATE TABLE table_1 (
  2. `userid` VARCHAR(2),
  3. `date_accessed` DATE,
  4. `rank` INT,
  5. `country` VARCHAR(2)
  6. );
  7. INSERT INTO table_1
  8. (`userid`, `date_accessed`, `rank`, `country`)
  9. VALUES
  10. ('A.', '2019-01-01', 1, 'US'),
  11. ('B.', '2019-01-02', 1, 'FR'),
  12. ('A.', '2019-01-03', 2, 'US'),
  13. ('A.', '2019-01-04', 3, 'US'),
  14. ('B.', '2019-01-04', 2, 'FR');

小提琴:https://www.db-fiddle.com/f/cafeo5c4try1q7fawhn7mc/1
我的数据集如下所示:

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

这是一组with INTERNATIONE\ table as语句的结果—但在我的查询中,这是我正在处理的结构。
对于每个用户,我想用排名2减去排名1的日期。所以对于用户a,我想得到介于1/1和1/4之间的datediff。理想情况下,输出如下所示:

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

最终的目标是获得每个国家的平均日期差异,即该国家所有用户的平均日期差异。

yyhrrdl8

yyhrrdl81#

对于这个示例数据,一种方法是 GROUP BY user, state 并使用条件聚合减去日期:

  1. SELECT
  2. user,
  3. DATEDIFF(
  4. MAX(CASE WHEN `rank` = 2 THEN date END),
  5. MAX(CASE WHEN `rank` = 1 THEN date END)
  6. ) date_diff,
  7. state
  8. FROM tablename
  9. WHERE `rank` IN (1, 2)
  10. GROUP BY user, state

请看演示。
结果:

  1. > user | date_diff | state
  2. > :--- | --------: | :----
  3. > A. | 3 | NY
  4. > B. | 1 | CA
展开查看全部

相关问题