问根据整个数据的总数选择前10名，按日期分组
EN

Stack Overflow用户

提问于 2015-08-12 08:23:53

回答 3查看 164关注 0票数 0

我有一个简单的表格，记录在线电台的客户端连接。我正在尝试产生一个视图，将返回前10个国家/地区，基于整个期间的收听时间，但按日期和日期分组。

需要澄清的是，前10名将是整个期间的前10名，而不仅仅是每天的前10名。例如，美国可能一直是我们的第一大国家，但有几天它可能会下降到12个，我仍然需要每天列出整体前10个国家。

实际上，这应该会产生一个视图，其中每天都会有相同的十个国家/地区，但包含总的监听小时数。

我创建了一个返回所有国家/地区的视图：

SELECT DATE( datetime_end ) , country_code, SUM( duration ) /3600
FROM icecast_logs
WHERE mount = 'live'
GROUP BY DATE( datetime_end ) , country_code

mysql

sql

回答 3

Stack Overflow用户

发布于 2015-08-12 08:40:14

对于MSSQL，使用TOP

SELECT TOP 10 DATE(datetime_end), country_code, SUM(duration)/3600
FROM icecast_logs
WHERE mount = 'live'
GROUP BY DATE(datetime_end), country_code
ORDER BY SUM(duration)/3600 DESC

对于MySQL，请使用LIMIT

SELECT DATE(datetime_end), country_code, SUM(duration)/3600
FROM icecast_logs
WHERE mount = 'live'
GROUP BY DATE(datetime_end), country_code
ORDER BY SUM(duration)/3600 DESC
LIMIT 10

对于Oracle，您需要使用RANK和ROWNUM

WITH  top_icecast_logs AS
(
    SELECT DATE(datetime_end) AS Dateend, country_code, SUM(duration)/3600 AS SumTotalAmount, 
    RANK () OVER (ORDER BY SUM (SumtotalAmount) DESC) AS tsum
    FROM icecast_logs
    GROUP BY DATE(datetime_end), country_code
)
SELECT    Dateend, country_code, SumTotalAmount
FROM      top_icecast_logs 
WHERE     tsum <= 2
ORDER BY  SumTotalAmount DESC;

票数 1

Stack Overflow用户

发布于 2015-08-12 09:09:53

我设法通过使用内部连接解决了这个问题：

SELECT DATE( datetime_end ) , icecast_logs.country_code, SUM( duration ) /3600 FROM icecast_logs
INNER JOIN
(SELECT country_code
FROM icecast_logs
WHERE mount =  'live'
GROUP BY country_code
ORDER BY SUM( duration ) DESC 
LIMIT 10) AS TopTen
ON icecast_logs.country_code = TopTen.country_code

WHERE mount =  'live'
GROUP BY DATE( datetime_end ) , icecast_logs.country_code

票数 0

Stack Overflow用户

发布于 2015-08-12 09:17:19

据我所知，你需要每天统计前十名听众(这里:国家)的所有时间。

首先，我们选取具有最高duration值的10个国家/地区代码，然后每天打印这10个国家/地区中每个国家的统计数据。

请记住，如果您没有10个国家/地区，CTE将提取不到10行。此外，如果某个国家已经有一段时间没有在听，它不会出现在你的结果中。这可以通过以下方式来处理:首先生成日期，然后将您的数据连接到这些日期，以显示每个日期中的每个国家/地区，比如duration_hrs中的0值

这应该会给你预期的结果。

WITH top_ten_all_time AS (
SELECT
   country_code
FROM
   icecast_logs
WHERE
   mount = 'live'
GROUP BY country_code
ORDER BY SUM(duration) / 3600 DESC
FETCH FIRST 10 ROWS ONLY
)
SELECT
   DATE(a.datetime_end) AS date_x,
   a.country_code,
   SUM(a.duration) / 3600 AS duration_hrs
FROM
   icecast_logs a
   INNER JOIN top_ten_all_time b USING (country_code)
WHERE
   mount = 'live'
GROUP BY DATE(a.datetime_end), a.country_code
ORDER BY date_x, country_code