联接表以计算日期之间的值 [英] Joining tables that compute values between dates

问题描述

所以我有以下两个表

表A

Date        num
01-16-15    10
02-20-15    12
03-20-15    13

表B

Date        Value
01-02-15    100
01-03-15    101
   .         .
01-17-15    102
01-18-15    103
   .         .
02-22-15    104
   .         .
03-20-15    110

我想创建一个在impala中具有以下输出的表

And i want to create a table that have the the following output in impala

Date         Value
01-17-15     102*10
01-18-15     103*10
02-22-15     104*12
   .           .
   .           .

因此，我们的想法是只考虑01-16-15和02-20-15之间的日期，以及02-20-15和03-20-15之间的日期.并使用该期间开始日期的数字，例如01-16-15，然后将其乘以该期间中每天的数字，即1-16至2-20.

So the idea is that we only consider dates between 01-16-15 and 02-20-15, and 02-20-15 and 03-20-15 exclusively. And use the num from the starting date of that period, say 01-16-15, and multiply it by everyday in the period, i.e. 1-16 to 2-20.

我知道应该通过加入来完成，但是我不确定在这种情况下如何加入. 谢谢！

I understand it should be done by join but I am not sure how do you join in this case. Thanks!

推荐答案

这可在MariaDb(MySql)上运行，并且非常基本，因此希望它也可以在impala上运行.

This works on MariaDb (MySql) and it's pretty basic so hopefully it works on impala too.

SELECT b.date, b.value * a.num
FROM tableB b, tableA a
WHERE b.date >= a.date
  AND (b.date < (SELECT MIN(c.date) FROM tableA c WHERE c.date > a.date)
       OR NOT EXISTS(SELECT c.date FROM tableA c WHERE c.date > a.date))

最后一个NOT EXISTS ...必须包含表A中最后一个日期之后的日期

The last NOT EXISTS... was needed to include dates after the last date in table A

更新 在问题的修订版本中，B中的日期永远不会大于A中最后一个日期(之后)，因此查询可以写为

Update In the revised version of the question the date in B is never larger (after) the last date in A so then the query can be written as

SELECT b.date, b.value * a.num
FROM tableB b, tableA a
WHERE b.date >= a.date
  AND b.date <= (SELECT MIN(c.date) FROM tableA c WHERE c.date > a.date)

这篇关于联接表以计算日期之间的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！