You are welcome to discuss the optimization of a statistical algorithm in the database)

About a statistical algorithm:

The T table structure is as follows:

A B C D
1 2 3 1
4 5 6 1
1 2 2 1
2 1 2 2
1 2 3 2
1 1 1 2

Requirement: count the numbers of A = 1, B = 2, C = 1 and B = 2 by Group D!
The following is based on your own algorithm.

 

 

-- Create a test script below

Create table t (A int, B int, C int, D int)

--- Test Data
While (1 = 1)
Begin
Insert into t
Select * from
(
Select convert (int, rand () * 1000),
Convert (int, rand () * 1000) B,
Convert (int, rand () * 1000) c,
Convert (int, rand () * 1000) d
) V
End

 

Title

-- Algorithm -----------------------
Declare @ begin_date datetime
Declare @ end_date datetime
Select @ begin_date = getdate ()

Select tt. d, r1, r2, r3
From
(
Select distinct d from t
) Tt
Left join
(
Select d, count (*) r1 from t where a = 1 group by d
) Aa on tt. d = aa. d
Left join
(
Select d, count (*) r2 from t where B = 2 group by d
) Bb on tt. d = bb. d
Left join
(
Select d, count (*) r3 from t where c = 1 and B = 2 group by d
) Cc on tt. d = cc. d

Select @ end_date = getdate ()
Select datediff (MS, @ begin_date, @ end_date) as 'time/millisecond'

---------------------------------------------------------

 

 

Title

-- Algorithm B -------------------------------------------------
Declare @ begin_date datetime
Declare @ end_date datetime
Select @ begin_date = getdate ()

Select d, sum (rr1) rr1, sum (rr2) rr2, sum (rr3) rr3
From
(
Select d, case when a = 1 then 1 else 0 end rr1,
Case when B = 2 then 1 else 0 end rr2,
Case when c = 1 and B = 2 then 1 else 0 end rr3
From t
) Tt
Group by d

Select @ end_date = getdate ()
Select datediff (MS, @ begin_date, @ end_date) as 'time/millisecond'

 

 

With the increase of statistical conditions, the expansion of algorithms, the advantages and disadvantages of each algorithm, or any better algorithms, let's discuss them!

Sorry, I am in a hurry, so I put it on the homepage. Thank you for your support!