SQL Query - Simple Question
-
Hi guys,
We got a table in a form like this:
Imagine it has millions of records! I'm looking for a simple query which fills the '?' mark for 'diffValue' column!
'diffValue' represents the difference between the value of 'sesSentOctets' of the current row and the previous row (previous minute for the same 'userName'). For instance for the last record, it must result in 140 (1688 - 1548).
Because we have millions of records, I want a very fast query for that! I know it is solvable by functions or other inner SELECT commands, but they are slow! I want it to be done with just one SELECT if it is possible!
Many thanks in advance.
-
Hi, could you post DDL and sample data to give you a complete solution?
The common way before 2012+ versions is to have a self join using row_number() over(partition by customer order by time).
-
This will do what you want but I have assumed that the sample will be taken consistently every minute. If this isn't the case another approach will be needed. As Luis said, if you post sample data and DDL, it will make it easier to answer your question.
create table #temp_Octets(
userName varchar (10)
,[time] time
,sesSentOctets int
,diffValue int
)
insert into #temp_Octets
select '9999998899','10:09',218,null union all
select '9999998899','10:10',372,null union all
select '9999998899','10:11',540,null union all
select '9999998899','10:12',708,null union all
select '9999998899','10:13',876,null union all
select '9999998899','10:14',1044,null union all
select '9999998899','10:15',1212,null union all
select '9999998899','10:16',1380,null union all
select '9999998899','10:17',1548,null union all
select '9999998899','10:18',1688,null;
update #temp_Octets
set diffValue = (
select
diffValue = #temp_Octets.sesSentOctets-ct.sesSentOctets
from #temp_Octets ct
where
#temp_Octets.time = dateadd(minute,1,ct.time)
)
select * from #temp_Octets
drop table #temp_Octets
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Also this looks painfully like QA in SQL 2000. Can you confirm which version of sql server you are using? The answers are going to be vastly different if you are running 2000.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks guys,
Sorry I'm new in this forum, I don't know how to post 'sample data', But what BWFC generated as data exactly represents my situation! I just got millions of records in SQL 2000 (migrating to 2012)
@BWFC,
Yeah, it's a solution, but it executes one SELECT for each record! it is very slow!
Beside, as you mentioned, it is not consistently every minute! there's up to 20 minutes differences between records.
-
I didn't read the op properly code posted below
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
I'm really not sure what you mean by 'doing it in one select'. As far as I can see you'll have to check the preceding row in order to establish what value you need to subtract. How you'd do this in one I don't know. However, the solution below will work in 2000 and also account for the difference in sampling times.
create table #temp_Octets(
userName varchar (10)
,[time] time
,sesSentOctets int
,diffValue int
)
insert into #temp_Octets
select '9999998899','10:09',218,null union all
select '9999998899','10:10',372,null union all
select '9999998899','10:11',540,null union all
select '9999998899','10:12',708,null union all
select '9999998899','10:13',876,null union all
select '9999998899','10:14',1044,null union all
select '9999998899','10:15',1212,null union all
select '9999998899','10:16',1380,null union all
-----Rows below updated with new times----
select '9999998899','10:36',1548,null union all
select '9999998899','10:58',1688,null;
-----Update process
create table #temp_Update
(
RowNo int identity(1,1)
,[time] time
,sesSentOctets int
)
insert into #temp_Update
select
t.time
,t.sesSentOctets
from #temp_Octets t
order by t.time
update #temp_Octets
set diffValue = (
select
ct.sesSentOctets-ct1.sesSentOctets
from #temp_update ct
join #temp_update ct1 on ct1.rowno = ct.rowno -1
where
ct.time = #temp_Octets.time
)
select * from #temp_Octets
drop table #temp_update ,#temp_Octets
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ok so if we assume that you are using SQL 2012 then you have the new Lead and LAG window functions
this calculates the difference from the previous row.
create table #temp_Octets
(
userName varchar (10)
,[time] time
,sesSentOctets int
)
insert into #temp_Octets
select '9999998899','10:09',218 union all
select '9999998899','10:10',372 union all
select '9999998899','10:11',540 union all
select '9999998899','10:12',708 union all
select '9999998899','10:13',876 union all
select '9999998899','10:14',1044 union all
select '9999998899','10:15',1212 union all
select '9999998899','10:16',1380 union all
select '9999998899','10:17',1548 union all
select '9999998899','10:18',1688;
SELECT
*
,sesSentOctets - LAG(sesSentOctets,1) OVER (PARTITION BY UserNAME ORDER BY TIME) as Diffvalue
FROM
#temp_Octets
drop table #temp_Octets
In earlier versions its simple to do but you need to do something like the following.
WITH CTE_RS
AS
(
SELECT
*
,Row_NUMBER() OVER (order by [time]) Rn
FROM
#temp_Octets
)
SELECT
CurrentRow.*
,PrevRow.sesSentOctets
,currentRow.sesSentOctets-PrevRow.sesSentOctets as Diff
FROM
CTE_RS CurrentRow
left join cte_rs PrevRow
ON PrevRow.Rn=currentRow.Rn-1
This wont work in 2000, so you would have put the CTE as a 2 Correlated sub queries.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Jason-299789 (12/1/2014)
This wont work in 2000, so you would have put the CTE as a 2 Correlated sub queries.Or maybe a temp table, since the Row_Number function doesn't exist in SQL 2000, so an identity column in a temp table may be needed as an alternate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (12/1/2014)
Jason-299789 (12/1/2014)
This wont work in 2000, so you would have put the CTE as a 2 Correlated sub queries.Or maybe a temp table, since the Row_Number function doesn't exist in SQL 2000, so an identity column in a temp table may be needed as an alternate.
Thanks Gail, I'd forgotten the Row_NUMBER function wasnt available in 2000,
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
How about using the Quirky Update? This method has been mentioned as "unsupported" but that's just a special reason to understand and test correctly. Read the following article to fully understand it. http://www.sqlservercentral.com/articles/T-SQL/68467/
Here's an example on how to do it.
--Generation of sample data
create table Octets
(
userName varchar (10)
,[time] time
,sesSentOctets int
,diffValue int
);
insert into Octets
select '9999998899','10:09',218,null union all
select '9999998899','10:10',372,null union all
select '9999998899','10:11',540,null union all
select '9999998899','10:22',709,null union all
select '9999998899','10:23',876,null union all
select '9999998899','10:24',1041,null union all
select '9999998899','10:35',1212,null union all
select '9999998899','10:36',1387,null union all
select '9999998899','10:47',1548,null union all
select '9999998899','10:58',1688,null union all
select '1111111111','10:23',876,null union all
select '1111111111','10:24',1044,null union all
select '1111111111','10:35',1222,null union all
select '1111111111','10:36',1380,null union all
select '1111111111','10:47',1748,null union all
select '1111111111','10:58',3688,null;
--Generation of sample data ends here and solution starts.
CREATE TABLE #temp_Octets
(
userName varchar (10)
,[time] time
,sesSentOctets int
,diffValue int
);
CREATE CLUSTERED INDEX CItemp_Octets ON #temp_Octets( username, [time]);
INSERT #temp_Octets
SELECT * FROM Octets;
DECLARE @diffValue int,
@Octets int,
@user varchar(10);
UPDATE o SET
@diffValue = diffValue = CASE WHEN @user = UserName THEN sesSentOctets - @Octets END,
@Octets = sesSentOctets,
@user = UserName
FROM #temp_Octets o WITH (TABLOCKX)
OPTION( MAXDOP 1);
SELECT *
FROM #temp_Octets;
DROP TABLE #temp_Octets;
GO
DROP TABLE Octets; --Clean my test environment.
EDIT: The code won't work as it is on SQL 2000 because of the time column. You need to use the correct data type.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply