0

I am using alasql in node.js, and I cannot get a join to work.

Here you have dummy data:

x = [ 
{ date: 20180501, price: 23, product: 'x' },
{ date: 20180501, price: 46, product: 'y' },
{ date: 20180502, price: 29, product: 'x' },
{ date: 20180502, price: 50, product: 'y' },
{ date: 20180503, price: 22, product: 'x' },
{ date: 20180503, price: 43, product: 'y' },
{ date: 20180504, price: 21, product: 'x' },
{ date: 20180504, price: 43, product: 'y' },
{ date: 20180505, price: 26, product: 'x' },
{ date: 20180505, price: 48, product: 'y' }]

I would like to get, for each day, the ratio between the price of product y and product y. So, my desired output is:

desiredOutput = [ 
{ date: 20180501, price_ratio: 46/23},  
{ date: 20180502, price_ratio: 50/29},  
{ date: 20180503, price_ratio: 43/22},
{ date: 20180504, price_ratio: 43/21},
{ date: 20180505, price_ratio: 48/26}]

I am attempting to get this with the following query:

alasql("select date, price_y/price_x as price_ratio from (select date, price as price_y from ? where product='y') as y join (select date, price as price_x from ? where product='x') as x on x.date=y.date", [x,x])

But I can't get it to work. It doesn't crash or anything, but I only get price_x, not the ratio. This is what I get:

[ 
{ date: 20180501, price_x: 23 },
{ date: 20180502, price_x: 29 },
{ date: 20180503, price_x: 22 },
{ date: 20180504, price_x: 21 },
{ date: 20180505, price_x: 26 } ]

I can get the desired result by running and storing each subquery separately, and then performing the join using those objects, but I would like to know how to do it with just nested subqueries, in a single call.

Any help would be appreciated!

Daniel Ortega
  • 75
  • 5

1 Answers1

0
/* to get produt x and y prices for each date */
const res = alasql("select date, sum(case when product='x' then price else null end) as price_x, sum(case when product='y' then price else null end) as price_y from ? group by date", [x])

/* to get ratio */
const res1 = alasql("select date, concat(price_y, '/', price_x) price_ratio from (select date, sum(case when product='x' then price else null end) as price_x, sum(case when product='y' then price else null end) as price_y from ? group by date)", [x])

console.log(res)
console.log(res1)

EDIT: added DEMO

Rico Chen
  • 2,260
  • 16
  • 18
  • Thanks :) But this is not what I wanted. Is there any way to get the join statement to work properly? – Daniel Ortega Jun 01 '18 at 07:18
  • Updated my answer, you asked for y/x but I gave you x/y, maybe that's the part that didn't satisfy your expectation? – Rico Chen Jun 01 '18 at 11:00
  • Not really :) The thing is, as I said in my question, I CAN obtain the desired results by means of an intermediate object, but what I want to achieve is getting the join statement to work. I can't seem to get a join statement joining subqueries with where clauses to work. – Daniel Ortega Jun 04 '18 at 07:51
  • `res1` is independent, you don't need `res` to get to res1 in my code, I wrote it that way so hopefully you understand how I get there. – Rico Chen Jun 04 '18 at 11:28