How to Create daily click, weekly click, and monthly click statistics (Stored Procedure Implementation)

 

        In a recent article publishing system, the customer requires that each article have daily clicks, weekly clicks, and monthly clicks. Although many articles have been published, such click statistics have not been made, I searched the internet, and many of my friends asked this question. Unfortunately, I didn't find the answer. I tried to find myself, so I decided to study it myself and finally achieved this effect, the specific implementation method is as follows:
        Data Table: the required fields of the document attributes are omitted. The statistical fields are as follows: day-to-day Update Time, week-to-week update time of weektime, month-to-month update time, total number of clicks of hits, and day-to-day clicks of dayhits, weekly hits of weekhits, monthly hits of monthhits, and total hits of hits.
        Idea: the daytime is the daily update time (that is, the time used to determine whether the news is today), and then compare the Time of the currently clicked article with daytime. If the current time and daytime are not one day, set dayhits to 1 and Add 1 to dayhits in one day. Similarly, compare the current time with weektime/monthtime for weeks/months, and add weekhits/monthhits to 1 or set it to 1, finally, set the update time to the current time (remember !).
The brief code is as follows (this code is implemented by storage ):

-- Function: counts the clicks of novels (daily clicks, weekly clicks, monthly clicks, and total clicks) -- Author: Jiang -- modification time: 2011-12-3use txbook -- determine whether the stored procedure already exists if exists (Select name from sysobjects where name = 'add _ hits 'and type = 'P ') drop procedure add_hitsgo -- create procedure add_hits (-- Define parameters, novel ID @ bookid INT) as -- define local variables, the last time the novel was clicked, declare @ daytime datetime declare @ weektime datetime declare @ monthtime datetime select @ daytime = [daytime], @ weektime = [weektime], @ monthtime = [monthtime] from [Book] Where [bookid] = @ bookid ------------------------- update date click if (datediff (day, @ daytime, getdate () = 0) -- The interval between days is equal to 0 days (that is, the day) begin update [Book] Set [dayhits] = [dayhits] + 1 where [bookid] = @ bookid -- if it is a day, it increments by endelse begin update [Book] Set [dayhits] = 1, [daytime] = getdate () Where [bookid] = @ bookid -- daily click returns 1, reset time end -------------------------- update week click if (datediff (Week, @ weektime, getdate ()) = 0) -- the number of separated weeks is equal to 0 weeks (that is, the week) begin update [Book] Set [weekhits] = [weekhits] + 1 where [bookid] = @ bookid endelse begin update [Book] Set [weekhits] = 1, [weektime] = getdate () Where [bookid] = @ bookid end -------------------------- click if (datediff (month, @ monthtime, getdate () = 0) -- the number of months at intervals is equal to 0 (the current month) begin update [Book] Set [monthhits] = [monthhits] + 1 where [bookid] = @ bookid endelse begin update [Book] Set [monthhits] = 1, [monthtime] = getdate () where [bookid] = @ bookid end -------------------- update total click Update [Book] Set [Hits] = [Hits] + 1 where [bookid] = @ bookid go

The test has been verified!