Weekly and monthly downloads

 

Generally, download sites must have this requirement. This week's download rankings are counted, and this month's download rankings are counted. In addition, some traffic statistics sites may have this week's IP addresses, and this month's IP addresses, it should be noted that this week's download, this month's download, is different from the last 7 days, the last month (30) download is different, the last 7 days download, is to give you a moment, so the download statistics from this time point seven days ago to this time point are mostly 7 days, and the download time of this week is related to the time given to you, if the given time is Sunday, then this time is the download statistics from AM to AM on Sunday for a total of 8 hours. If the time is Saturday, that is the statistics for the seven days of the week.

 

As we mentioned above, we need to calculate the daily ranking, weekly ranking, and monthly ranking. Therefore, the minimum ranking statistics granularity should be one day. If we need to calculate by time period, the minimum statistics granularity should be one hour,

The above requirements are as follows:

Daily Download count table (software ID (INT), download count (INT), date (datetime ))

 

The following operations are performed for each download action:

 

Create proc daily table _ add
@ Software ID int
As

Declare @ existid int
Set @ existsid = 0
Select @ existsid = software ID from [daily table] Where @ software id = software ID and datediff (DD, date, getdate () = 0
If @ existsid! = 0
Begin
Update ....
End
Else
Begin
Insert .....
End
////

Based on the above data structure, there are the following weekly ranking statistics, with monthly ranking statistics

Create proc daily table _ statbyweek

@ Datepoint datetime --- given time point

As

Select software ID, sum (download times) as amount from [daily table] Where datediff (wk, date, @ datepoint) = 0 group by software ID order by amount DESC

 

-Monthly Statistics

 

Create proc daily table _ statbyweek

@ Datepoint datetime --- given time point

As

Select software ID, sum (download times) as amount from [daily table] Where datediff (mm, date, @ datepoint) = 0 group by software ID order by amount DESC

 

Note that the input parameter @ datepoint is the length of time from the beginning of this week (month) to the current time point. If it is a time point in the previous week (month, then, the statistics will be collected for the complete week of the week or month at the input time point.

 

-- Statistics for 7 consecutive days of Download

 

Create proc daily table _ statbyserialdays

 

@ Datepoint datetime,

@ Daynum int -- consecutive days

As

Select software ID, sum (downloads) amount from [daily table] Where datediff (DD, date, @ datepoint) <= @ daynum group by software ID order by amount DESC

 

--------------

In addition, if you have performance requirements, you should create a weekly table and a monthly table.

Weekly table (software ID, number of downloads, Update Time (datetime ))

Monthly table (software ID, number of downloads, Update Time (datetime ))

In addition to updating the daily table, each download action also updates the weekly table and monthly table.

UpdatedAlgorithmSimilar to the preceding daily table _ add

Only change the data in datediff to wk and mm respectively to find the data of the same week or month.

In statistics, we also use datediff to find the download record set for the same week or month at the same time (no group is required here)