sql - 使用要考虑的最大行数聚合 SQL 查询

Question

我正在尝试找出 SQL 查询。

我有一张包含 team_id、ip_address 和 date_voted 字段的投票数据表，我需要返回每个 的投票数code>team_id 但只计算任何 24 小时内每个 IP 地址的前 10 行。

Answer 1

还没来得及检查，但下面应该可以解决问题。

SELECT Yr, DoY, team_id, SUM(IF NbVote < 10, NbVote, 10) As FilteredVoteCount
FROM (
  SELECT YEAR(date_voted) AS Yr, DAYOFYEAR(date_voted) AS DoY, 
    team_id, 
    ip_address,
    COUNT(*) AS NbVotes
  FROM myTable
  -- WHERE here for some possible extra condition.
  GROUP BY YEAR(date_voted), DAYOFYEAR(date_voted), team_id, ip_address
)
GROUP BY Yr, DoY, team_id
ORDER BY Yr, DoY, team_id   -- or some other order may be desired.