MySQl - SQL - 每天前 5 条记录

Question

对于下面的数据集，我试图获取 MySQL 数据库上每天排名前 5 的记录。这是一个网页访问表，我的目标是找出 5 个访问量最大的页面。

我很乐意只获取给定日期范围内的前 10 名，但是无法设法获取针对相关主题的查询。

我确实尝试了以下方法

select 
    VISIT_DATE,
    group_concat(PAGE_ID order by NUM_VISITS desc separator ',') as pagehits
from 
    PAGEVISITS
where 
    VISIT_DATE >= '2015-07-01' and VISIT_DATE <= '2015-07-15'
group by
    VISIT_DATE

但是我在这里无法获取SUM(NUM_VISITS) int，并且无法获取group byVISIT_DATE`，这使得它毫无用处。分开了，这就是我已经走了多远

select 
    VISIT_DATE,
    PAGE_ID,
    SUM(NUM_VISITS) as pagehits
 from
   PAGEVISITS
 where 
   VISIT_DATE >= '2015-01-01' and VISIT_DATE <= '2015-03-15'
 group by
   VISIT_DATE,
   PAGE_ID
order by
    pagehits desc
limit 5;

这显然不是每天的前 5 名。另外，可能有多个页面最终具有相同的页面点击数，并且也可能最终显示为前 5 个页面之一，这就是为什么我尝试使用 group concat 来显示所有页面 ID 的数量页面点击量位于当天页面点击量的前 5 名。

我不是一个经验丰富的 SQL 程序员。我可以请求帮助来完成这项工作吗？如果我在任何地方听起来不够清楚，请告诉我。

CREATE TABLE PAGEVISITS
    (`VISIT_DATE` date, `PAGE_ID` varchar(20),  `SERVER_NAME` varchar(50), `NUM_VISITS` int)
;

INSERT INTO PAGEVISITS
    (`VISIT_DATE`, `PAGE_ID`, `SERVER_NAME`, `NUM_VISITS`)
VALUES
('2015-01-01','2015A12123','A',10),
('2015-01-01','2015A12123','B',10),
('2015-01-01','2015A12124','A',30),
('2015-01-01','2015A12124','B',30),
('2015-01-01','2015A12125','A',40),
('2015-01-01','2015A12125','B',40),
('2015-01-01','2015A12126','A',1),
('2015-01-01','2015A12126','B',1),
('2015-01-01','2015A12127','A',0),
('2015-01-01','2015A12127','B',1),
('2015-01-01','2015A12128','A',40),
('2015-01-01','2015A12129','A',30),
('2015-01-01','2015A12134','A',45),
('2015-01-01','2015A12126','A',56),
('2015-01-01','2015A12167','A',23),
('2015-01-01','2015A12145','A',17),
('2015-01-01','2015A121289','A',12),
('2015-01-01','2015A121289','B',5),
('2015-01-02','2015A12123','A',3),
('2015-01-02','2015A12124','A',10),
('2015-01-02','2015A12125','A',70),
('2015-01-02','2015A12126','A',10),
('2015-01-02','2015A12127','A',100),
('2015-01-02','2015A12128','A',3),
('2015-01-02','2015A12128','B',2),
('2015-01-02','2015A12129','A',10),
('2015-01-02','2015A12134','A',5),
('2015-01-02','2015A12126','A',6),
('2015-01-02','2015A12167','A',3),
('2015-01-02','2015A12145','A',170),
('2015-01-02','2015A121289','A',34),
('2015-01-03','2015A12123','A',34),
('2015-01-03','2015A12124','A',14),
('2015-01-03','2015A12125','A',37),
('2015-01-03','2015A12126','A',23),
('2015-01-03','2015A12127','A',234),
('2015-01-03','2015A12128','A',47),
('2015-01-03','2015A12129','A',67),
('2015-01-03','2015A12134','A',89),
('2015-01-03','2015A12134','B',1),
('2015-01-03','2015A12126','A',97),
('2015-01-03','2015A12167','A',35),
('2015-01-03','2015A12145','A',0),
('2015-01-03','2015A121289','A',19), 
('2015-01-04','2015A12123','A',115),
('2015-01-04','2015A12124','A',149),
('2015-01-04','2015A12125','A',370),
('2015-01-04','2015A12126','A',34),
('2015-01-04','2015A12127','A',4),
('2015-01-04','2015A12128','A',70),
('2015-01-04','2015A12129','B',70),
('2015-01-04','2015A12134','A',70),
('2015-01-04','2015A12126','B',64),
('2015-01-04','2015A12167','A',33),
('2015-01-04','2015A12145','A',10);

预期输出

fiddle here

Answer 1

如果要每天使用它，那么您应该考虑创建一个单独的表并使用过程在其中填充数据。还有更好的方法来做到这一点(使用合并)。这仅供您引用。

create table daily_results
(`VISIT_DATE` date, `PAGE_ID` varchar(20),  `SERVER_NAME` varchar(50), `NUM_VISITS` int);

CREATE PROCEDURE proc_loop_test( IN startdate  date,  in enddate  date)
BEGIN

     WHILE(startdate < enddate) DO
        insert into daily_results (select * from PAGEVISITS where VISIT_DATE=startdate order by NUM_VISITS desc limit 5);
        SET startdate = date_add(startdate, INTERVAL 1 DAY);
     end WHILE;
END;

使用调用

call proc_loop_test(`2015-01-01`,`2015-03-15`);
select * from daily_results;