October 21, 2013 at 6:20 pm
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:
cjohn2013/10/01 00:30:00 5
fpears2013/10/03 00:11:00 2
cjohn2013/10/04 12:30:45 3
kthomas2013/10/04 13:20:11 3
cjohn2013/10/04 14:10:10 2
rpeter2013/10/04 18:01:01 3
cjohn 2013/10/04 19:10:01 10
fpears2013/10/06 19:11:11 11
cjohn2013/10/07 10:11:03 20
rpeter2013/10/09 05:10:05 11
fpears2013/10/11 06:10:15 6
cjohn2013/10/14 13:11:11 7
kthomas2013/10/16 08:10:10 6
I want to get the daily, weekly, monthly and yearly average of usage/per user
October 21, 2013 at 6:48 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 21, 2013 at 9:02 pm
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
4028/# No. of users
4148/# No. of users
and so on..
October 21, 2013 at 9:13 pm
Almost there.
So does cjohn count as 1 user on 04 Oct or as 2?
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 21, 2013 at 10:31 pm
He would be considered as 1 user only.
October 21, 2013 at 11:00 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 22, 2013 at 4:28 am
Hi,
Dwain's query will solve your requirement.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply