I have a table which has data that appears as:
Staion Date Temperature
A 2015-07-31 8
B 2015-07-31 6
C 2015-07-31 8
A 2003-02-21 4
B 2003-02-21 7
C 2003-02-21 7
For each date I need to create arrays so that it has the following combination:
c1 = (A + B)/2, c2 = (A + B + C)/3 and c3 = (B + C)/2
Right I am doing three different inner join
on the table itself and doing a final inner join
to achieve the following as result:
Date c1 c2 c3
2015-07-31 7 7.33 7
2003-02-21 5.5 6 7
Is there a cleaner way to do this?
4条答案
按热度按时间ijxebb2r1#
No need for a
JOIN
, you could simply use aGROUP BY
and an aggregation function:q3qa4bjr2#
SUM
Function is very useful in such cases:ma8fv8wu3#
You can do it with just two joins and almost literally the formulas you've provided:
Result:
8tntrjer4#
You can use pivot and calculation on pivoted data as below:
Your input table: