Conditions in aggregate/sum functions

2 posts / 0 new
Last post
rasel200209
rasel200209's picture
Offline
Last seen: 3 years 1 month ago
Joined: Nov 24 2009
Junior Boarder

Posts: 2

Rasel Ahmed
Conditions in aggregate/sum functions

we use many forms of this...

SUM( (case when (year = '2009' and week<=40
OR year = '2008' and week>=1) then 1 else 0
end) * myvalue ) as TYEAR

so there is a condition in the Sum function.

can infini enhance the speed where there are Case in the Sum?

jtommaney
jtommaney's picture
Offline
Last seen: 4 weeks 5 hours ago
Joined: Oct 27 2009
Expert Boarder

Posts: 58

Jim Tommaney
Re:Conditions in aggregate/sum functions

The cost of the operation is likely related to the # of qualifying rows * the number of operations. You may be able to reduce the time slightly if you can simplify the number of steps in the expression. Here I have a compound date field I can express as a between clause rather than the compound logic show below.

[code]mysql> select sum(case when lo_orderdate between 19920101 and 19920630
-> then 1 else 0 end * lo_extendedprice) 1992_Q1_Q2_sum, count(*)
-> from lineorder where lo_orderdate <= 19921231;
+--------------------+----------+
| 1992_Q1_Q2_sum | count(*) |
+--------------------+----------+
| 138804076164817.00 | 91248844 |
+--------------------+----------+
1 row in set (5.90 sec)

mysql>
mysql>
mysql> select sum(case when (year = 1992 and month <= 6 ) and (year = 1992 and month >= 1)
-> then 1 else 0 end * lo_extendedprice) 1992_Q1_Q2_sum, count(*)
-> from lineorder where lo_orderdate <= 19921231;
+--------------------+----------+
| 1992_Q1_Q2_sum | count(*) |
+--------------------+----------+
| 138804076164817.00 | 91248844 |
+--------------------+----------+
1 row in set (7.81 sec)

mysql> select sum(lo_extendedprice) 1992_Q1_Q2_sum, count(*)
-> from lineorder where lo_orderdate <= 19921231;
+--------------------+----------+
| 1992_Q1_Q2_sum | count(*) |
+--------------------+----------+
| 279113147753549.00 | 91248844 |
+--------------------+----------+
1 row in set (2.29 sec)

mysql> select sum(1 * lo_extendedprice) 1992_Q1_Q2_sum, count(*) from lineorder where lo_orderdate <= 19921231;
+--------------------+----------+
| 1992_Q1_Q2_sum | count(*) |
+--------------------+----------+
| 279113147753549.00 | 91248844 |
+--------------------+----------+
1 row in set (4.54 sec)
[/code]

There is definitely some overhead for inclusion of even a trivial function expression.
I'm not sure what else to recommend for you without access to the data model/queries.

Thanks - Jim Tommaney
Chief Product Architect