SQL：如何连接两个表，并按时间戳提取数据？

ewm0tg9j 于 2022-09-18 发布在 Java

关注(0)|答案(2)|浏览(158)

我正在使用MySQL。我有两个表，一个是关于电影类型的，另一个是关于带时间戳的电影评级的。我想要将这两个表与电影ID连接在一起，以计算每种类型电影的平均评级。我试图只提取每部电影至少有10个收视率的电影类型和12月份的收视率，并按平均评分从高到低排序。

表‘TYPE’
MovieID|类型

1|戏剧
2|冒险
3|喜剧性
...|...

表‘评级’

MovieID|收视率|时间戳
-|-|
1|1|851786086
2|1.5|1114306148
1|2|1228946388
3|2|850723898
1|2.5|1167422234
2|2.5|1291654669
1|3|851345204
2|3|944978286
3|3|965088579
3|3|1012598088
1|3.5|1291598726
1|4|1291779829
1|4|850021197
2|4|945362514
1|4.5|1072836909
1|5|881166397
1|5|944892273
2|5|1012598088
...|...|...

预期结果：(NB评级>=10，12月给出评级)

Type|avg_Rating

戏剧性|3.45

我正在尝试编写如下所示的查询，但无法执行它。(原表中约1万个数据)我应该在哪里调整查询？

SELECT DISTINCT T.type, AVG(R.rating) FROM types AS T
INNER JOIN ratings AS R ON T.movieId = R.movieId
WHERE R.timestamp LIKE (
    SELECT FROM_UNIXTIME(R.timestamp,'%M') AS Month FROM ratings
    GROUP BY Month
    HAVING Month = 'December')
GROUP BY T.type
HAVING COUNT(R.rating) >=10
ORDER BY AVG(R.rating) DESC;

mysql

来源：https://stackoverflow.com/questions/73757499/sql-how-to-join-two-tables-and-extract-the-data-by-timestamp

2条答案

按热度按时间

cetgtptt1#

I see two problems:

timestamp LIKE - what's that supposed to do? and
inner query with GROUP BY by without any aggregation. Perhaps you meant WHERE? And anyway you don't need it at all - just do the same check for December directly on timestamp, w/o LIKE and w/o subquery

SELECT DISTINCT T.type, AVG(R.rating) FROM
types AS T INNER JOIN ratings AS R 
ON T.movieId = R.movieId
WHERE FROM_UNIXTIME(R.timestamp,'%M')  = 'December' 
GROUP BY T.type
HAVING COUNT(R.rating) >=10
ORDER BY AVG(R.rating) DESC;

赞(0）回复(0）举报 2022-09-18

ryevplcw2#

You can try next query.

SELECT DISTINCT T.type, AVG(R.rating) FROM types AS T
INNER JOIN ratings AS R ON T.movieId = R.movieId
GROUP BY T.type
HAVING 
    COUNT(R.rating) >= 10 -- have 10 or more rating records
    AND SUM(MONTH(FROM_UNIXTIME(R.timestamp)) = 12) > 0 -- have at least one rating in December
ORDER BY AVG(R.rating) DESC;

sqlize

赞(0）回复(0）举报 2022-09-18

我来回答

SQL：如何连接两个表，并按时间戳提取数据？

2条答案

相关问题

热门标签

最新问答