Fetch spec similar to sql window function in postgress

#1

Can the C3 IoT platform perform window functions in postgress? 

I'm trying to do a group by, with sums of certain fields, and the first value of other fields, in a single query. This is possible with normal SQL. For example. SELECT product, sum(sales), first_value(price) OVER (ORDER BY order_date desc) FROM sales_table GROUP BY product. That will aggregate all data to one row-per-product, with that product's total sales and most recent price. 

For example, suppose I map this data to c3type SalesOrder. 

product sales price order_date
A 10 1.0 2018-07-01
A 20 1.5 2018-07-02
A 10 1.6 2018-07-04
B 5 5.0 2018-07-01
B 6 5.0 2018-07-02
B 5 5.5 2018-07-03

Then, I like to get the below result by running a single fetch call.

product sum(sales) first_value(price)
A 40 1.6
B 16 5.5

 

0 Likes