java - Count the daily clicks of the top ten ads from 20170403 to 20170420

Question

There is an advertisement table advertise(advertise_id, date, count), the data is as follows:

I need to calculate the number of clicks per day for the top 10 ads within a certain period of time.

The result of the query should be that there are 10 pieces of data with different advertise_id every day. How should this SQL be written?

天蓬老师 · Answer

There is a problem that the number of clicks on the advertising date that does not appear in the record is not displayed. If you want to display it, you need to construct this part of the data first. If it is not displayed, just use the following sql

select a.* from advertise a join (select advertise_id,sum(count) sm from advertise group by advertise_id order by sm desc limit 10) b on a.advertise_id=b.advertise_id where a.date BETWEEN 20170403 AND 20170420 order by a.date,a.count;

ringa_lee · Answer

SELECT s.date,s.advertise_id,s.count FROM advertise_stat s
WHERE
    EXISTS (
        SELECT advertise_id FROM (SELECT advertise_id FROM advertise_stat GROUP BY advertise_id ORDER BY count DESC LIMIT 10) AS advertise_temp
        WHERE advertise_id = s.advertise_id
    )
AND s.date BETWEEN 20170403 AND 20170420
ORDER BY s.date ASC,s.count DESC