mysql - 找出每个年龄段中最常见的类型

Question

我正在考虑按十年划分年龄组，并找出更常见的类型。这比我预期的要难，但这是我尝试过的:

一个表是这样的，叫做:sell_log

id id_film id_cust
1   2      2
2   3      4
3   1      5
4   4      3
5   5      1
6   2      4
7   2      3
8   3      1
9   5      3

第二个是关于具有 id 和流派的 films 的表格:

id_film      genres
    1        comedy
    2        fantasy
    3        sci-fi
    4        drama
    5        thriller

和第 3 个表，customers 是这样的:

id_cust  date_of_birth_cust
1          1992-03-12
2          1999-06-25
3          1986-01-14
4          1985-09-18
5          1992-05-19

这是我做的代码:

select  id_cust,date_of_birth_cust,
    CASE
    WHEN date_of_birth_cust > 1980-01-01 and date_of_birth_cust < 1990-01-01 then ##show genre##
    WHEN date_of_birth_cust > 1990-01-01 and date_of_birth_cust < 2000-01-01 then ##show genre##
    ELSE ##show genre##
    END
from purchases 
INNER JOIN (
    select  id_cust 
    FROM sell_log
    group by id_cust
  ) customer.id_cust = sell_log.id_cust

你认为正确的形式是怎样的？

预期结果:例如根据最常见的流派数量找到该流派并将其传递给该年龄段。

ages                most frequent genre
from 1980 to 1990     comedy
from 1990 to 2000     fantasy
rest ages             drama

更新:执行答案中的代码会给出以下结果:

ages                most_frequent_genre
from 1980 to 1989   Comedy
from 1990 to 1999   Thriller
from 1990 to 1999   Action
from 1990 to 1999   Comedy
rest                Comedy

我做错了什么

Answer 1

您可以使用 CTE获得每个年龄段和流派的结果，然后用它来获得每个年龄段的最大购买数量。最后再次加入 CTE:

with cte as (
  select 
    CASE
      WHEN year(c.date_of_birth_cust) between 1980 and 1989 then 'from 1980 to 1989'
      WHEN year(c.date_of_birth_cust) between 1990 and 1999 then 'from 1990 to 1999'
      ELSE 'rest'
    END ages,
    f.genres,
    count(*) counter
  from sell_log s
  inner join films f on f.id_film = s.id_film 
  inner join customers c on c.id_cust = s.id_cust
  group by ages, f.genres
)

select c.ages, c.genres most_frequent_genre
from cte c inner join (
  select c.ages, max(counter) counter 
  from cte c
  group by c.ages
) g on g.ages = c.ages and g.counter = c.counter
order by c.ages

参见 demo .
在您的示例数据中，所有结果都会有联系。
结果:

| ages              | most_frequent_genre |
| ----------------- | ------------------- |
| from 1980 to 1989 | fantasy             |
| from 1990 to 1999 | comedy              |
| rest              | fantasy             |