Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query regarding AVG Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:06 PM
Points: 3, Visits: 4

i want help regarding a sql query. I receive app usage data from users whenever they try to access and the time they spend on the app. I've the following columns:

username date (datetime) usage in min

Example data:

cjohn 2013/10/01 00:30:00 5
fpears 2013/10/03 00:11:00 2
cjohn 2013/10/04 12:30:45 3
kthomas 2013/10/04 13:20:11 3
cjohn 2013/10/04 14:10:10 2
rpeter 2013/10/04 18:01:01 3
cjohn 2013/10/04 19:10:01 10
fpears 2013/10/06 19:11:11 11
cjohn 2013/10/07 10:11:03 20
rpeter 2013/10/09 05:10:05 11
fpears 2013/10/11 06:10:15 6
cjohn 2013/10/14 13:11:11 7
kthomas 2013/10/16 08:10:10 6



I want to get the daily, weekly, monthly and yearly average of usage/per user
Post #1506934
Posted Monday, October 21, 2013 6:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 PM
Points: 3,627, Visits: 5,273
I think you need to be a little more clear about your expected output. To help out, here is you sample data in consumable form.

WITH SampleData AS
(
SELECT userid, [date], usage
FROM
(
VALUES ('cjohn','2013/10/01 00:30:00',5)
,('fpears','2013/10/03 00:11:00',2)
,('cjohn','2013/10/04 12:30:45',3)
,('kthomas','2013/10/04 13:20:11',3)
,('cjohn','2013/10/04 14:10:10',2)
,('rpeter','2013/10/04 18:01:01',3)
,('cjohn','2013/10/04 19:10:01',10)
,('fpears','2013/10/06 19:11:11',11)
,('cjohn','2013/10/07 10:11:03',20)
,('rpeter','2013/10/09 05:10:05',11)
,('fpears','2013/10/11 06:10:15',6)
,('cjohn','2013/10/14 13:11:11',7)
,('kthomas','2013/10/16 08:10:10',6)
) a (userid, [date], usage)
)
SELECT userid, [date]=CAST([date] AS DATE), usage
FROM SampleData;


Now taking a look just at userid=cjohn, his records appear as follows:

userid  date         usage
cjohn 2013-10-01 5
cjohn 2013-10-04 3
cjohn 2013-10-04 2
cjohn 2013-10-04 10
cjohn 2013-10-07 20
cjohn 2013-10-14 7



So his average daily usage over the period 01-Oct to 14-Oct is going to be different than if these are his only records for all of 2013.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1506941
Posted Monday, October 21, 2013 9:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:06 PM
Points: 3, Visits: 4
I just gave an example of the data. The data will be spread across multiple years, and possibly every user will have usage data very random throughout the day/week/month/year. In the end, I want daily usage,weekly,monthly and yearly usage for all the users combined in DB i.e. the end result would be:

For the sample data that I posted:

daily
=====
Date Avg Usage
10/01 5/# No. of users
10/03 2/# No. of users
10/04 21/# No. of users

Weekly (if my week starts on sun)
=====
Week # Avg Usage
40 28/# No. of users
41 48/# No. of users


and so on..
Post #1506952
Posted Monday, October 21, 2013 9:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 PM
Points: 3,627, Visits: 5,273
Almost there.

So does cjohn count as 1 user on 04 Oct or as 2?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1506954
Posted Monday, October 21, 2013 10:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:06 PM
Points: 3, Visits: 4
He would be considered as 1 user only.
Post #1506963
Posted Monday, October 21, 2013 11:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 PM
Points: 3,627, Visits: 5,273
You probably want to do something like this then:

WITH SampleData AS
(
SELECT userid, [date], usage
FROM
(
VALUES ('cjohn','2013/10/01 00:30:00',5)
,('fpears','2013/10/03 00:11:00',2)
,('cjohn','2013/10/04 12:30:45',3)
,('kthomas','2013/10/04 13:20:11',3)
,('cjohn','2013/10/04 14:10:10',2)
,('rpeter','2013/10/04 18:01:01',3)
,('cjohn','2013/10/04 19:10:01',10)
,('fpears','2013/10/06 19:11:11',11)
,('cjohn','2013/10/07 10:11:03',20)
,('rpeter','2013/10/09 05:10:05',11)
,('fpears','2013/10/11 06:10:15',6)
,('cjohn','2013/10/14 13:11:11',7)
,('kthomas','2013/10/16 08:10:10',6)
) a (userid, [date], usage)
)
SELECT userid, [date]=CAST([date] AS DATE), usage
INTO #Temp
FROM SampleData

-- By day
SELECT [date], AvgUsage=(1.0 * SUM(usage)) / COUNT(DISTINCT userid)
FROM #Temp
GROUP BY [date];

-- By week
SELECT [week]=DATEADD(week, DATEDIFF(week, 0, [date]), 0)
,AvgUsage=(1.0 * SUM(usage)) / COUNT(DISTINCT userid)
FROM #Temp
GROUP BY DATEADD(week, DATEDIFF(week, 0, [date]), 0);

-- By month
SELECT [month]=DATEADD(month, DATEDIFF(month, 0, [date]), 0)
,AvgUsage=(1.0 * SUM(usage)) / COUNT(DISTINCT userid)
FROM #Temp
GROUP BY DATEADD(month, DATEDIFF(month, 0, [date]), 0);

-- By year
SELECT [month]=DATEADD(year, DATEDIFF(year, 0, [date]), 0)
,AvgUsage=(1.0 * SUM(usage)) / COUNT(DISTINCT userid)
FROM #Temp
GROUP BY DATEADD(year, DATEDIFF(year, 0, [date]), 0);

SELECT DATEADD(week, DATEDIFF(week, 0, '2013-09-28'), 0) -- This is Sat
,DATEADD(week, DATEDIFF(week, 0, '2013-09-29'), 0) -- This is Sun
,DATEADD(week, DATEDIFF(week, 0, '2013-09-30'), 0) -- This is Mon

GO
DROP TABLE #Temp;


The only thing tricky about this is the week reporting. The final select shows that Sundays are reported in the same week as the following Monday, but the date returned by the DATEADD construct is the Monday. This is going to be sensitive to your setting for DATEFIRST.

Play around with it and see if it helps.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1506966
Posted Tuesday, October 22, 2013 4:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
Hi,
Dwain's query will solve your requirement.
Post #1507043
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse