Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

charts of popularity (like music charts)

hi

given a table which logs everytime someone access an article

articlelog

id        integer (primary key)
articleid integer
userid    text(20)
date      shortdate


ie

1 amoran 34 25/02/2003
2 amoran 65 24/02/2003
3 amoran 2 24/02/2003
4 amoran 645 24/02/2003
5 amoran 554 23/02/2003
6 amoran 364 23/02/2003
7 amoran 344 23/02/2003

is there a way to generate a chart which says this weeks most 10 most popular articles (and if possible) what possition that article was at last week

so the current week would be the last seven days

thanks
0
amoran
Asked:
amoran
  • 7
  • 6
  • 6
  • +2
3 Solutions
 
PanimuCommented:
Yeah just group by article ID with count and order by count. Limit by current week or previous week for week by week analysis.
0
 
amoranAuthor Commented:
but id like to do something like

this week - last week - articleid
1 - 3 - 24
2 - 2 - 12
3 - 1 - 29
4 - / - 30

(where / means it wasnt in last weeks top ten)

etc
0
 
adatheladCommented:
Hi.
Try this:

CREATE TABLE #ptblLastWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

CREATE TABLE #ptblThisWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

'-- Get last week's TOP 10
INSERT #ptblLastWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 1
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

'-- Get this week's TOP 10
INSERT #ptblThisWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 0
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

SELECT tw.position "ThisWeek",
     ISNULL((SELECT CAST(lw.position AS VARCHAR)FROM #ptblLastWeek lw WHERE lw.articleid = tw.articleid), '/') "LastWeek",
     tw.articleid
FROM #ptblThisWeek tw

DROP TABLE #ptblLastWeek
DROP TABLE #ptblThisWeek
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
amoranAuthor Commented:
i got a syntax error i n create table statement
0
 
adatheladCommented:
sorry, the problem isnt in the CREATE TABLE statement, it's where I've put the comments.

Find these 2 comments in my query:
'-- Get last week's TOP 10
'-- Get this week's TOP 10

and remove the single quote at the beginning of each line.
i.e.:
-- Get last week's TOP 10
-- Get this week's TOP 10


0
 
Scott PletcherSenior DBACommented:
I suggest making it a permanent table.  You don't want the chart somehow changing between weeks.  You could either keep separate rows for current and last or one row with columns for current and prior week (denormalized, but should be OK here as it shouldn't change).
0
 
adatheladCommented:
amoran, did you get the query to work?
0
 
PanimuCommented:
Making a permanent table is bad management as the data can always be constructed from other data.
0
 
Scott PletcherSenior DBACommented:
There are exceptions to every rule.  Knowing when it is worthwhile to break the rules is the key to being a good DBA, programmer, etc..  In my view, in this situation it is worth it, because the list from the prior week MUST remain EXACTLY as it was reported then.  Futhermore, this info will likely be accessed frequently enough that it isn't worth the overhead to continually re-compute it.
0
 
amoranAuthor Commented:
thanks for all your help

isnt this table created and dropped everytime some accesses this webpage?

is there a way to do it without creating and dropping tables?

thanks again
0
 
adatheladCommented:
Yes, the table is created and dropped each time the procedure is called - temporary tables allow temporary storage of results leading up to a final recordset.

Scott's suggestion is a very valid point and I'd recommend you incorporate what he said.
i.e.
- Create 1 permanent table (e.g. tblArticlePosition)
- At the end of each week, store the week's article positions in this table
- Then, change the procedure I gave to query this table to get the previous week's positions and generate the current week's data on-the-fly.
- This will mean you don't have to work out 2 week's worth of positions each time the web page is accessed so should increase speed/performance of the query.
0
 
PanimuCommented:
"prior week MUST remain EXACTLY as it was reported then"

And it will. Records of views will not change. If they do change then surely there is a good reason for this and the top 10 should change as well.
0
 
Scott PletcherSenior DBACommented:
Top Ten Record/Movie, etc., lists, once published, almost never change (I don't recall ever seeing it happen).  It would be too confusing if they did.  I suspect that there might sometimes be corrections to albums/tickets sold, etc., after the first list is published, but the original list remains as it was, and the current list accurtely reflects in which position that album or movie or whatever appeared on the prior week's list.

So, for example, when I view the "Weekend Top Box Office" on Yahoo, it comes up instantly because it's been pre-totalled and saved.  Sure they could derive it again, but that would be a terrible waste of resources.  It's cheaper overall simply to keep a permanent store of the totals.

Pre-determining and storing such info is somewhat like a form of OLAP reporting, for which pre-defined totals are also pre-accumulated and stored to speed up future queries of the data.  Yes, the total could be re-calculated, hundreds of times I guess if necessary, but that's not very practical.
0
 
PanimuCommented:
That's tradition, and music tradition at that. This aint music top 10 and there's no reason to stick to that tradition.

Unless he wants too, his call.

But even then could have a status on the data row so that instead of deleting you change it to "inactive" and have your top 10 routine count inactive ones toward the total still.
0
 
Scott PletcherSenior DBACommented:
Certainly he could.  I just don't think he should.  

Yahoo could also re-add all movie receipts everytime someone requested a list of the top movies for the past week/weekend.  But that would take so long that most people, including me, would probably quick asking for it because it took too long to get.
0
 
PanimuCommented:
Speed is a good point.

Maybe the lists could be reconstructed on a scheduled basis?
0
 
Scott PletcherSenior DBACommented:
The previous week's list is, in my view, static.

Obviously we aren't going to agree on this, so I'm going to quit taking up space in this question on this topic.  Everyone should have enough info by now to decide for themselves which approach they prefer.
0
 
PanimuCommented:
Well, the previous week is only as static as the data with which it was constructed.

That should be static. If it's not, why not? If the why not is a really good reason then that reason should be good enough that the top 10 also needs ammending.

But yeah, enough of his space. :) If you'd like to bitch at me tho feel free at bitch@panimu.com *huggles*
0
 
amoranAuthor Commented:
"Speed is a good point.

Maybe the lists could be reconstructed on a scheduled basis? "

would there be a way to create two tables in my db (say every sunday at 9pm - i dont know if sheduling is possible) 'lastweek' and 'thisweek' and just pull the data from there) and every week theyd automatically be updated?

thanks!
0
 
adatheladCommented:
You would only want to store Last Weeks positions in a permanent table. The current week's positions must be derived on-the-fly as they will be changing.

Assuming you *only* want to store the previous week's positions (i.e. dont want 2 weeks previous details):

To create the table:
CREATE TABLE tblPreviousPositions
(
Position INTEGER IDENTITY(1,1),
ArticleId INTEGER
)

Stored procedure to be scheduled weekly to update permanent table:
CREATE PROCEDURE stp_RefreshPreviousPositions
AS
BEGIN
DELETE FROM tblPreviousPositions
DBCC CHECKIDENT(tblPreviousPositions, RESEED, 1)

INSERT tblPreviousPositions(articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 1
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC
END

You can then package a call to this stored procedure into a DTS package. Once you have created the DTS package, simply schedule it to run once a week, every sunday at 9pm.

0
 
adatheladCommented:
This would mean your query would be:

CREATE TABLE #ptblThisWeek
(
position INTEGER IDENTITY(1,1),
articleid INTEGER
)

-- Get this week's TOP 10
INSERT #ptblThisWeek (articleid)
SELECT TOP 10 articleid
FROM articlelog
WHERE DATEDIFF(wk, [date], GETDATE()) = 0
GROUP BY articleid
ORDER BY COUNT(articleid) DESC, articleid ASC

SELECT tw.position "ThisWeek",
    ISNULL((SELECT CAST(lw.position AS VARCHAR)FROM tblPreviousPositions lw WHERE lw.articleid = tw.articleid), '/') "LastWeek",
    tw.articleid
FROM #ptblThisWeek tw

DROP TABLE #ptblThisWeek
0
 
amoranAuthor Commented:
"You can then package a call to this stored procedure into a DTS package. Once you have created the DTS package, simply schedule it to run once a week, every sunday at 9pm."

How would I do this?

Thanks again
0
 
adatheladCommented:
1) In Enterprise Manager, right click the Data Transformation Services folder and click "New Package...".
This will take you into design mode where you set up the package.

2)You need to add a connection to your database. Do this by clicking the icon in the side toolbar on the left of the screen that looks like a server (if you hover the mouse over it, it says "Microsoft OLE DB Provider For SQL Server"). Then enter your server, database and username/password details in the box that appears. Give the connection a name (e.g. "DatabaseName") - this goes in the first box in the popup window. Click Ok.

3)Now click the icon in the side tool bar that looks like a cylinder with 2 red arrows in a circle (tooltip says "Execute SQL Task"). In the window that pops up, enter details for the connection to use, description of the task, and in the SQL Statement box enter the call to your stored proc e.g. EXECUTE stp_RefreshPreviousPositions.
Click Ok.

4) Save the package. In Enterprise Manager, right click your package (in the Local Packages section) and choose "Schedule Package...". This will display a screen to take you through scheduling it.

Cheers
0
 
Scott PletcherSenior DBACommented:
adathelad:

To confirm, yes I was only referring to the prior week's list as permanent, up until whatever point the current week's list becomes "final".  For some brief interim period, it's possible that both would be pulled from pre-totalled data.
0
 
CleanupPingCommented:
amoran:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now