June 13, 2012 at 12:25 am
Hello,
I am using SQL 2k8 R2. My colleague would like query that will return unique value if all the supplied (or column) values are same else return 1.
One way I write the query as below:
SELECT CASE WHEN COUNT(DISTINCT a) = 1
THEN SUM(DISTINCT a) / COUNT(DISTINCT a)
ELSE 1
END
FROM ( SELECT 220
UNION ALL
SELECT 220
UNION ALL
SELECT 220
UNION ALL
SELECT 220
) a ( a )
Above pattern will be used in original query which already has "Group By" clause for grouping of data (sum, avg etc..).
Is there any alternate method for the same that will give best result?
Thanks
June 13, 2012 at 2:07 am
SELECT TOP 1
CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END
FROM (
SELECT a,
CountInGroup= COUNT(*),
SumGroup= SUM(a),
GroupCount= COUNT(*) OVER(PARTITION BY @@spid)
FROM ( SELECT 220 UNION ALL
SELECT 220 UNION ALL
SELECT 220 UNION ALL
SELECT 221 UNION ALL
SELECT 220
) a ( a )
GROUP BY a
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 13, 2012 at 2:40 am
Is that really any better Chris? I make it, at best the same or slightly worse.
Test 1
SELECT *
INTO #test
FROM (SELECT 221
UNION ALL
SELECT TOP 1000000 220
FROM master.sys.all_columns a, master.sys.all_columns b,
master.sys.all_columns c)a(a);
PRINT 'CHRIS';
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 1
CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END
FROM (
SELECT a,
CountInGroup= COUNT(*),
SumGroup= SUM(a),
GroupCount= COUNT(*) OVER(PARTITION BY @@spid)
FROM #test a
GROUP BY a
) d;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('-',80);
PRINT 'HARDIK';
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CASE WHEN COUNT(DISTINCT a) = 1
THEN SUM(DISTINCT a) / COUNT(DISTINCT a)
ELSE 1
END
FROM #test a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
CHRIS
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_______________________________________________________________________________________________________________000000000028'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 313 ms, elapsed time = 82 ms.
--------------------------------------------------------------------------------
HARDIK
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_______________________________________________________________________________________________________________000000000028'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 47 ms.
Test 2
SELECT *
INTO #test
FROM (--SELECT 221
--UNION ALL
SELECT TOP 1000000 220
FROM master.sys.all_columns a, master.sys.all_columns b,
master.sys.all_columns c)a(a);
PRINT 'CHRIS';
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 1
CASE WHEN GroupCount = 1 THEN SumGroup / CountInGroup ELSE 1 END
FROM (
SELECT a,
CountInGroup= COUNT(*),
SumGroup= SUM(a),
GroupCount= COUNT(*) OVER(PARTITION BY @@spid)
FROM #test a
GROUP BY a
) d;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('-',80);
PRINT 'HARDIK';
PRINT REPLICATE('-',80);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CASE WHEN COUNT(DISTINCT a) = 1
THEN SUM(DISTINCT a) / COUNT(DISTINCT a)
ELSE 1
END
FROM #test a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
CHRIS
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 3, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_______________________________________________________________________________________________________________000000000029'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 66 ms.
--------------------------------------------------------------------------------
HARDIK
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_______________________________________________________________________________________________________________000000000029'. Scan count 5, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 142 ms, elapsed time = 46 ms.
The results are similar even if we add an index.
With no index, this is worse than both of the solutions above: -
SELECT CASE WHEN a = b THEN a ELSE 1 END
FROM (SELECT MAX(a), MIN(a)
FROM #test) b(a,b);
--------------------------------------------------------------------------------
TEST
--------------------------------------------------------------------------------
Table '#test_______________________________________________________________________________________________________________000000000035'. Scan count 1, logical reads 1609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 282 ms, elapsed time = 277 ms.
However, if we add an index.
CREATE NONCLUSTERED INDEX nc_idx_a_test ON #test (a);
Then run all three again.
CHRIS
--------------------------------------------------------------------------------
Table 'Worktable'. Scan count 3, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 1, logical reads 1863, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 225 ms.
--------------------------------------------------------------------------------
HARDIK
--------------------------------------------------------------------------------
Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 1, logical reads 1863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 154 ms.
--------------------------------------------------------------------------------
TEST
--------------------------------------------------------------------------------
Table '#test_______________________________________________________________________________________________________________000000000030'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
June 13, 2012 at 4:52 am
Cadavre (6/13/2012)
Is that really any better Chris? I make it, at best the same or slightly worse...
You're absolutely right.
Even with the window function removed, the original code is faster. It simply looks like it might be inefficient.
Never underestimate the value of a good test.
Cadavre, you're clearly bored! 😛
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 13, 2012 at 5:15 am
Thanks ChrisM@Work , Cadavre
I am using original query which I have shared 🙂
Thanks
June 13, 2012 at 6:50 am
ChrisM@Work (6/13/2012)
You're absolutely right.Even with the window function removed, the original code is faster. It simply looks like it might be inefficient.
Never underestimate the value of a good test.
Cadavre, you're clearly bored! 😛
I certainly was this morning 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply