December 31, 2010 at 4:48 am
Hello, I am working on a report where I have to show which part of a video was viewed mostly by all users. In other words I have to show this as graphical report in which it will cover complete video lengh and show how much users get interest in viewing which part of the video.
Here is the actual data we are collecting: (lets say if a video is of length 70 seconds)
EventsNameEventSequenceIdParametersNameSlider Value VideoId UserId
Play2CurrentPosition0122
Stop3CurrentPosition70122
Play4CurrentPosition0122
Pause5CurrentPosition10.83122
Play6CurrentPosition10.8122
Pause7CurrentPosition14.376122
Play8CurrentPosition15.7122
Pause9CurrentPosition21.496122
Play10CurrentPosition51.8122
Pause11CurrentPosition55.923122
This is one single user viewing for complete session. There is pair of Play and Stop or Play and Pause which gives idea from where user started and up till that slider position the video has been watched.
Idea:
1- Depending on each video length i have to divide the x-axis scale. Lets say if the video is for 50 mins then the x-axis scale should be (50/50) for 1 minute each.
3- with this scale I have to keep data in their exact scale if it lies inside that position then i have to fill a record for that in a temp table. so that the graph may be ploted easily.
2- Every viewing should be a complete pair (play/Stop or Play/Pause)
Here is some briefing about the user viewing to explain how a user watched this video :
video IdUserIdviewing Position
1220-70 (viewed once completely - Pair of Play till Stop)
1220-10.8 (viewed initial part of 10 sec)
12210.8-14.4 (slider on same positon and continued viewing till 14.4)
12215.7-21.5 (slider bit moved ahead and started viewing from 15.7 till 21.5)
12252-56 (Here slider was jumped to 52 second and viewed up till 56 seconds possion)
Please help me how can i fill up the table so that graph can be ploted easily. The desired data from above records and sample graph is attached here.
Shamshad Ali.
December 31, 2010 at 5:45 am
Shamshad
What are you asking - how to populate the first table from information in the second, or vice versa, or something else?
John
December 31, 2010 at 5:45 am
Shamshad
What are you asking - how to populate the first table from information in the second, or vice versa, or something else?
John
December 31, 2010 at 5:55 am
Ali,
the links you provided seem to be broken. Neither can I find a picture of the data nor the graph. Please add the pictures as attachements to your post (there is a collapsed post option folder below the input window).
December 31, 2010 at 6:32 am
The first table (written with data with actual question not the red color table in image) is the information how the user viewing is stored in table. And if you see the image i have a lengthy table which is what desired to populate the graph.
Shamshad Ali.
December 31, 2010 at 6:42 am
OK, I think I understand. You've divided your 70 second video into 0.4 second slices, and you want to record on a graph how many times each of those slices has been hit. Is that right? So now you want to use the information in the red table in your attachment to populate the green table. Is that also right? If so, what are you struggling with - is it how to split the "viewing Position" data into a start time and end time, or is it how to work out which time slices were hit given a certain start time and end time? Please will you show us what you've tried so far.
By the way, how do you determine what size intervals to slice your video into?
John
December 31, 2010 at 6:46 am
yes you are right with your few comments but for actual data i have uploaded new file which is the actual Tracked data and explained visually to have to make output like desired table (green one) the earlier red color table was just for explaination -
Hope you better got it now.
Shamshad Ali.
December 31, 2010 at 6:52 am
Yes, I definitely get it. So now what we need from you is the following:
(1) Table DDL for all tables involved, including indexes, constraints, triggers etc
(2) Sample data from those tables, in the form of INSERT statements
(3) The query you have already written in your attempt to resolve this problem
Thanks
John
December 31, 2010 at 6:58 am
Here's a short example. Please note that I used full seconds instead of 0.4 frictions. But it should be easy to adjust that.
DDL and Sample data for those who'll continue to work on it included.
DECLARE @tbl TABLE
(
EventsName CHAR(5),
EventSequenceId INT,
ParametersName VARCHAR(100),
Slider_Val DECIMAL(6,3),
VideoId INT,
UserId INT
)
INSERT INTO @tbl
SELECT 'Play', 2 ,'CurrentPosition', 0, 1, 22 UNION ALL
SELECT 'Stop', 3 ,'CurrentPosition', 70, 1, 22 UNION ALL
SELECT 'Play', 4 ,'CurrentPosition', 0, 1, 22 UNION ALL
SELECT 'Pause', 5 ,'CurrentPosition', 10.83, 1, 22 UNION ALL
SELECT 'Play', 6 ,'CurrentPosition', 10.8, 1, 22 UNION ALL
SELECT 'Pause', 7 ,'CurrentPosition', 14.376, 1, 22 UNION ALL
SELECT 'Play', 8 ,'CurrentPosition', 15.7, 1, 22 UNION ALL
SELECT 'Pause', 9 ,'CurrentPosition', 21.496, 1, 22 UNION ALL
SELECT 'Play', 10 ,'CurrentPosition', 51.8, 1, 22 UNION ALL
SELECT 'Pause', 11 ,'CurrentPosition', 55.923, 1, 22
;WITH cte AS
(
SELECT t1.slider_val,t2.slider_val s2
FROM @tbl t1
INNER JOIN @tbl t2 ON t1.eventsequenceid=t2.eventsequenceid-1 AND t1.EventsName='Play'
),
cte2 AS
(
SELECT number
FROM cte
CROSS APPLY
(SELECT number FROM master..spt_values WHERE TYPE='P')x
WHERE number>slider_val AND number<=s2
)
SELECT number,COUNT(*) AS cnt
FROM cte2
GROUP BY number
December 31, 2010 at 7:01 am
To resolve this I am doing it in two parts:
First query to make data as shown in red table:
declare @userid int
declare @VideoId int
declare @LastUserId int
declare @LastVideoId int
declare @LastVideoTrackingId int
select @LastVideoTrackingId = VideoTrackingId from VideoTrackingChronicle where Id=1
declare VideoTrackingCursor CURSOR FOR
select VideoId, UserId from VideoTracking
where VideoTracking.Id >= @LastVideoTrackingId
group by VideoId, UserId
set @LastUserId = 0
set @LastVideoId = 0
OPEN VideoTrackingCursor
FETCH NEXT from VideoTrackingCursor into @VideoId, @userid
while ((@UserId <> @LastUserId) OR (@VideoId <> @LastVideoId))
begin
set @LastUserId = @userid
set @LastVideoId = @VideoId
insert into VideoTrackingTransitional
SELECT MT.Id
, MT.VideoId
, MT.VideoTitle
, MT.EstimatedDuration
, MT.UserId
, PlayPosition = ISNULL((Select top 1
[VideoTrackingParametersValue]
from [VideoTrackingSubDetail]
inner join VideoTrackingDetail on VideoTrackingDetail.Id = VideoTrackingSubDetail.VideoTrackingDetailId
inner join VideoTracking on VideoTracking.Id = VideoTrackingDetail.VideoTrackingId
where [VideoTrackingDetail].[VideoTrackingEventsName] = 'Play'
and [VideoTrackingSubDetail].[VideoTrackingParametersId] = 1
and [VideoTracking].Id = MT.Id
order by [VideoTrackingSubDetail].[VideoTrackingParametersValue] asc
-- and [VideoTrackingSubDetail].Id= SubDetail.Id
),-1)
, PauseStopPosition = ISNULL((Select top 1
[VideoTrackingParametersValue]
from [VideoTrackingSubDetail]
inner join VideoTrackingDetail on VideoTrackingDetail.Id = VideoTrackingSubDetail.VideoTrackingDetailId
inner join VideoTracking on VideoTracking.Id = VideoTrackingDetail.VideoTrackingId
where [VideoTrackingDetail].[VideoTrackingEventsName] in ('Pause','Stop')
and [VideoTrackingSubDetail].[VideoTrackingParametersId] = 1
and [VideoTracking].Id = MT.Id
--and [VideoTrackingSubDetail].Id= SubDetail.Id
order by [VideoTrackingSubDetail].[VideoTrackingParametersValue] desc
),-1)
, StopPosition = 1--ISNULL((Select top 1
-- [VideoTrackingParametersValue]
-- from [VideoTrackingSubDetail]
-- inner join VideoTrackingDetail on VideoTrackingDetail.Id = VideoTrackingSubDetail.VideoTrackingDetailId
-- inner join VideoTracking on VideoTracking.Id = VideoTrackingDetail.VideoTrackingId
-- where [VideoTrackingDetail].[VideoTrackingEventsName] = 'Stop'
-- and [VideoTrackingSubDetail].[VideoTrackingParametersId] = 1
-- and [VideoTracking].Id = MT.Id
-- --and [VideoTrackingSubDetail].Id= SubDetail.Id
-- order by [VideoTrackingSubDetail].[VideoTrackingParametersValue] desc
-- ),-1)
FROM [VideoTrackingDetail]
inner join [VideoTrackingSubDetail] SubDetail on [VideoTrackingDetail].[Id] = SubDetail.[VideoTrackingDetailId]
inner join [VideoTracking] MT on [VideoTrackingDetail].VideoTrackingId = MT.Id
where
SubDetail.[VideoTrackingParametersId] = 1
and [VideoTrackingDetail].[VideoTrackingEventsName] in ('Play','Pause','Stop')
and MT.UserId = @userid
and MT.VideoId = @VideoId
group by MT.Id
, MT.VideoId
, MT.UserId
, MT.VideoTitle
, MT.EstimatedDuration
order by MT.Id
update VideoTrackingChronicle
set VideoTrackingChronicle.VideoTrackingId = (select top 1 VideoTrackingTransitional.VideoTrackingId from VideoTrackingTransitional order by VideoTrackingId desc)
where VideoTrackingChronicle.Id =1
FETCH NEXT from VideoTrackingCursor into @VideoId, @userid
End
CLOSE VideoTrackingCursor
DEALLOCATE VideoTrackingCursor
and then to produce green table I am using following query:
declare @VideoLength int
declare @tempLengthLower float
declare @tempLengthUpper float
declare @VideoTrackingId int
declare @LastVideoTrackingId int
declare @MaxPausePosition int
declare @MaxStopPosition int
declare VideoTransCursor CURSOR FOR
select VideoTrackingId from VideoTrackingTransitional
OPEN VideoTransCursor
FETCH NEXT from VideoTransCursor into @VideoTrackingId
set @LastVideoTrackingId = 0
while (@VideoTrackingId <> @LastVideoTrackingId)
begin
set @tempLengthLower = 0.00
set @tempLengthUpper = 10.0
set @LastVideoTrackingId = @VideoTrackingId
select @VideoLength=Max(EstimatedDuration) from VideoTrackingTransitional
where VideoId = (select VideoId from VideoTrackingTransitional where VideoTrackingId = @VideoTrackingId)
select @MaxPausePosition = MAX(PausePosition) from VideoTrackingTransitional
where VideoTrackingId = @VideoTrackingId
select @MaxStopPosition = MAX(StopPosition) from VideoTrackingTransitional
where VideoTrackingId = @VideoTrackingId
while ((@tempLengthUpper <=@VideoLength) and ((@tempLengthLower<=@MaxPausePosition) OR (@tempLengthLower<=@MaxStopPosition)))
begin
insert into VideoTrackingAggregate
select VideoTrackingTransitional.VideoId
, VideoTrackingTransitional.UserId
, @VideoTrackingId
, @tempLengthUpper
, Sum(case when (@tempLengthUpper between VideoTrackingTransitional.PlayPosition and VideoTrackingTransitional.PausePosition) then 1
--when (@tempLengthUpper between VideoTrackingTransitional.PlayPosition and VideoTrackingTransitional.StopPosition) then 1
else 0 end)
from VideoTrackingTransitional
where VideoTrackingTransitional.VideoTrackingId = @VideoTrackingId
and ((@tempLengthUpper between VideoTrackingTransitional.PlayPosition and VideoTrackingTransitional.PausePosition))
--OR (@tempLengthUpper between VideoTrackingTransitional.PlayPosition and VideoTrackingTransitional.StopPosition))
group by VideoTrackingTransitional.VideoTrackingId
, VideoTrackingTransitional.VideoId
, VideoTrackingTransitional.UserId
set @tempLengthUpper = @tempLengthUpper + 10
set @tempLengthLower = @tempLengthLower + 10
end
FETCH NEXT from VideoTransCursor into @VideoTrackingId
end
CLOSE VideoTransCursor
DEALLOCATE VideoTransCursor
This is costly and very lengthy with all manual process to fill up the table for graph. see the insert statements.
I will post the table structure and sample data as soon I am ready with this.
Shamshad Ali.
December 31, 2010 at 7:09 am
Lutz, The output is giving number from 1,2,3 up till 70. It should be 50 (Fixed) and its value should be the actual video length divided by 50 with increment in each row as the video size may vary from seconds/minutes to hours so we have to keep rows fixed i.e. 50 for all videos and for the graph to make sense.
Thanks.
December 31, 2010 at 7:11 am
Lutz, The output is giving number from 1,2,3 up till 70. It should be 50 (Fixed) and its value should be the actual video length divided by 50 with increment in each row as the video size may vary from seconds/minutes to hours so we have to keep rows fixed i.e. 50 for all videos and for the graph to make sense.
Thanks.
December 31, 2010 at 7:17 am
Why don't you give it a try and change the query?
The fix isn't that complicated...
January 1, 2011 at 12:35 am
Hello Lutz, I have fixed few things from your example as that was not exactly covering my real scenario- Anyway I had resolved them and I made following changes:
1- Limit the rows (scale) to 50 or whatever we setup scale for x-axix on graph
2- There must be other users in sample data we initially used userid 22 and i have added one another pair of viewing for userid 24.
3- The sequence IDs were not exactly in sequence as there were other events which were also captured during video watch but for this report we only have to collect (Play/Stop or Play/Pause) pair. So this I changed in the table and now the sample code looks like as follows:
DECLARE @tbl TABLE
(
EventsName CHAR(5),
EventSequenceId INT,
ParametersName VARCHAR(100),
Slider_Val DECIMAL(6,3),
VideoId INT,
UserId INT
)
declare @TotalLength decimal(10,3) = 70;
INSERT INTO @tbl
SELECT 'Play', 2 ,'CurrentPosition', 0, 1, 22 UNION ALL
SELECT 'Stop', 4 ,'CurrentPosition', 70, 1, 22 UNION ALL
SELECT 'Play', 7 ,'CurrentPosition', 0, 1, 22 UNION ALL
SELECT 'Pause', 8 ,'CurrentPosition', 10.83, 1, 22 UNION ALL
SELECT 'Play', 10 ,'CurrentPosition', 10.8, 1, 22 UNION ALL
SELECT 'Pause', 15 ,'CurrentPosition', 14.376, 1, 22 UNION ALL
SELECT 'Play', 18 ,'CurrentPosition', 15.7, 1, 22 UNION ALL
SELECT 'Pause', 21 ,'CurrentPosition', 21.496, 1, 22 UNION ALL
SELECT 'Play', 25 ,'CurrentPosition', 51.8, 1, 22 UNION ALL
SELECT 'Pause', 30 ,'CurrentPosition', 55.923, 1, 22 UNION ALL
SELECT 'Play', 5 ,'CurrentPosition', 0, 1, 24 UNION ALL
SELECT 'Stop', 8 ,'CurrentPosition', 70, 1, 24
;WITH cte AS
(
SELECT t1.slider_val,t2.slider_val stopPause
FROM (
select eventsequenceid, slider_val, userid,
(select Min(eventsequenceid) from @tbl where EventSequenceId > t.EventSequenceId and UserId = t.UserId) as NextValue
from @tbl t where t.EventsName ='Play') t1
INNER JOIN
(select eventsequenceid, slider_val, userid from @tbl where EventsName IN ('Stop', 'Pause')) t2
ON t2.eventsequenceid= t1.NextValue AND t2.userId = t1.userId -- AND t1.EventsName='Play'
),
cte2 AS
(
SELECT scale
FROM cte
CROSS APPLY
(SELECT (number*1.4) as scale FROM master..spt_values WHERE TYPE='P')x
WHERE scale>slider_val AND scale<= stopPause
)
SELECT scale,COUNT(*) AS cnt
FROM cte2
GROUP BY scale
Now, I need help on follwing:
1- Is my written query in first CTE is OK from the performance point of view or there is any other better way to produce the pair of (Play/Stop and Play/Pause)?
2- If I have multiple videos i.e. thousands of videos and thousands of users, with billions of such rows in Video tracking table, should this be a performance issue OR I may run a job to store data in a table ready for UI everytime (but that would be not realtime). What you best suggest?
Shamshad Ali.
January 1, 2011 at 4:34 am
If you're talking about such a table size, you should apply several changes:
Don't use 'Play','Stop' and the like. Make it a tinyint number and use a Lookup-Table if needed.(Edit: Gianluca Solomon Rutzky provided an excellent article[/url] related to this subject). Check if you really need the value [ParametersName]. If so, use a separate lookup table, too.
If you don't need the 3 digit precision of the slider_val, reduce it.
Verify, if INT for [EventSequenceId] is the prooper data type for the table size you're expecting.
You might even consider changing your current table design to store the pairs in one row right away (INSERT if 'PLAY' and 'UPDATE' if 'STOP'/'PAUSE'). That would allow you to query the table directly without any additional overhead and as a side effect it would reduce the table size even more.
Now to your original question:
I'm expecting you need to plot the graph for a limited number of rows only (e.g. per video or per video/user). In that case I would call a stored procedure that will store the required data in a temp table (with an identity column to get it numbered in sequence) and use the approach I posted earlier (If you change your design to store pairs in one row, you wouldn't need the self reference in the cte though...).
The approach you use is called a "Trinagular Join" which will definitely kill performance.
Edit: typo fixed
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply