4

I'm wondering how to mix selecting columns, aggregated values as well as 'selects-from-partitions'. Please let me know if this is a duplicate question!

An example of what I mean:

Given example transaction log:

ID     Food    Date     Cost
1      Apple   5/1/14   10
1      Apple   5/4/14   8
1      Apple   5/6/14   15
1      Pear    5/2/14   25
2      Apple   5/3/14   15

The goal is to select for each unique ID/Food combination, the cost of the first purchase (by date), last purchase as well as average cost.

The output would look like:

ID   Food    First_Cost    Last_Cost     Avg_Cost
1    Apple   10            15            11
1    Pear    25            25            25
2    Apple   15            15            15

I've looked into OVER - PARTITION (Select first row in each GROUP BY group?) as well as grouping alongside aggregate functions (https://dba.stackexchange.com/questions/41494/select-first-row-grouping-add-aggregate-function) but neither seemed to answer this specific question.

This is a code example of what I'm trying to accomplish: 

select id
      ,food
      ,firstrow(cost) OVER(PARTITION BY id, food ORDER BY date asc as first_cost
      ,avg(cost) from table
group by id, food;

Using the ROWNUMBER() OVER(PARTITION...)and then filtering with the where-clause doesn't seem to work either since the aggregate functions would be off.

One solution could always be to create a table with the first, one with last, one with average and join the three together, but I'm looking for something cleaner.

EDIT: From jpw's comment, Aster does have the first_value() window function. However, the issue with it is that the columns that I order or partition by must be included in the groupby clause. His suggestion and one solution could be to change all aggregate functions into window functions and eliminate the groupby altogether.

Would still like to see a cleaner solution or confirmation that this isn't possible!

Community
  • 1
  • 1
so13eit
  • 942
  • 3
  • 11
  • 22
  • 1
    What RDBMS are you using? – Mureinik Feb 25 '15 at 21:16
  • I've tagged this as 'Aster'.. that's Teradata's Aster – so13eit Feb 25 '15 at 21:17
  • 1
    With MS SQL I think you could have used the `first_value()` window function like in this example http://www.sqlfiddle.com/#!6/0e51c/1 but I guess it might not be applicable with the Teradata database (or maybe it is, I don't know anything about it). – jpw Feb 25 '15 at 21:24
  • Hi jpw- I looked into it and Aster has the first_value() window function as well! Could you help expand/answer how I would use it? Specifically, it's saying that the date must be in the Group By clause... – so13eit Feb 25 '15 at 21:47
  • @so13eit I had that problem with MSSQL too so I removed the group by altogether and applied avg() as a window function too - you can see in the example SQL fiddle I linked. Maybe that can work. – jpw Feb 25 '15 at 23:46
  • select id ,food ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as first_cost ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date desc) as last_cost ,avg(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as avg_cost from sm_fruits – skmathur Apr 11 '16 at 23:21

2 Answers2

0

Query:

select id
      ,food
      ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as first_cost
      ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date desc) as last_cost
      ,avg(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as avg_cost
from sm_fruits

gets you close:

    id  Food    first_cost  last_cost   avg_cost
1   1   Apple   10          15          11.00
2   1   Apple   10          15          11.00
3   1   Apple   10          15          11.00
4   1   Pear    25          25          25.00
5   2   Apple   15          15          15.00
skmathur
  • 1,587
  • 5
  • 14
  • 21
-2

you can cross apply the table to its self (twice) to get the max/min date and the cost of the item for that date.

Read up on the cross apply its supper useful

 select id
      ,food
      ,LastCost
      ,FirstCost
      ,avg(cost) from TABLE t1
       Cross APPLY(
                    SELECT MAX(date), cost AS LastCost FROM TABLE t2 WHERE t2.food = t1.food
                  ) maxdate
       Cross APPLY(
                    SELECT MIN(date), cost AS FirstCost FROM TABLE t3 WHERE t3.food = t1.food
                  ) mindate
group by id, food;
Mike
  • 1