javascript - 在 node.js 中使用 alasql 加入两个包含 where 子句的 select 语句

Question

我在 node.js 中使用 alasql，但无法加入工作。

这里有虚拟数据：

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' }]

我想每天获得产品 y 和产品 y 的价格之间的比率。所以，我想要的输出是：

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}]

我试图通过以下查询得到这个：

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])

但我无法让它工作。它不会崩溃或任何东西，但我只得到 price_x，而不是比率。这就是我得到的：

[ 
{ 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 } ]

我可以通过分别运行和存储每个子查询，然后使用这些对象执行连接来获得所需的结果，但我想知道如何在一次调用中仅使用嵌套子查询来完成它。

任何帮助，将不胜感激！

标签： javascriptnode.jsalasql

Answer 1

/* 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)

编辑：添加演示