sql - 如何创建一个标志，根据值是否占计数的大部分来获取列值的值？

Question

我有一张看起来像这样的 table ......

id    city        date
1     chicago     5/1
1     chicago     5/2
1     new york    5/1
2     new york    5/3
2     seattle      .
3     chicago      .
4     seattle      .
4     seattle      .

我想创建第三列，它采用“城市”的值，其中特定城市占单个 ID 条目数的大部分 (>51%)。因此，例如，id #1 将有 favorite_city = 'chicago'。我什至不知道从哪里开始......

非常感谢您的帮助。谢谢!

Answer 1

WITH
  summary As
(
  SELECT
    your_table.*,
    COUNT(*) OVER (PARTITION BY id)  AS id_count,
    COUNT(*) OVER (PARTITION BY id, city)  AS id_city_count
  FROM
    your_table
)
SELECT
  summary.*,
  MAX(
    CASE WHEN id_city_count * 2 > id_count THEN city ELSE NULL END
  ) 
  OVER (PARTITION BY id)
FROM
  summary