加入表格时，请使用最新日期作为缺失日期 [英] Use most recent date for missing dates when joining tables

问题描述

我有2张桌子:

1.tran_test : (id, amount, currency(Currency_Name), date_1)
2.cur_test: (id, currency, date_2, price(amount In USD))

考虑到cur.test表中的某些日期缺失，如何获取每天的总销售额?

How to get the total sum of sales for each day considering that some of the dates in cur.test table are missing so you need to use the last available dates to calculate the result?

我写了一个查询来计算总和，但不包括缺少费率的日子.

I wrote a query that calculates the sum, but it excludes days where the rates are missing.

如何计算缺失天数?

SELECT date_1, SUM(amount*c.price) AS sum_by_day
 FROM tran_test AS t
 INNER JOIN cur_test AS c
 ON t.currency = c.currency AND date_1 = date_2
 GROUP BY date_1;

我不需要只生成日期.我需要使用表格中可用的数据来计算金额.如果缺少某些日期，那么在可以使用货币汇率的情况下，我需要取最后一个可用的日期.例如-表tran.test的交易记录是9月28日，但是没有提供这一天的货币汇率，因此我需要采用最近的汇率

I don't need to just generate dates. I need to calculate amounts using data available in the tables. If some of the dates are missing I need to take the last available date when currency rates are available. Eg - The table tran.test has a transaction for September 28, but currency rates for this day are not provided, so i need to take the last available rate

相似数据样本

INSERT INTO tran_test (amount, currency, date_1) VALUES 
(1000,'RUB','2018-09-01'),
(15,'USD','2018-09-01'),
(10,'EUR','2018-09-01'),

INSERT INTO cur_test (name,date_2, price) VALUES
('RUB','2018-01-09',0.02),
('USD','2018-01-09',1),

输出应该像 日期-该日期的总金额 2018-02-21-2128

Output should be like date - total amount for the date 2018-02-21 - 2128

完整代码在这里- https://www.db-fiddle.com/f/v4SHW9XgtgyehbhanQFRx5/2 实际数据稍微复杂一点，并且包括UNIX格式的日期

Full code is here - https://www.db-fiddle.com/f/v4SHW9XgtgyehbhanQFRx5/2 Actual data is little more complicated and includes dates in UNIX format

推荐答案

select date_1, SUM(amount*c.price) AS sum_by_day
from
(
    select *, max (date_2) as lastDate
    from tran_test AS t
    INNER JOIN cur_test AS c
    ON t.currency = c.currency AND date_1 >= date_2
    GROUP BY date_1;
) as t
INNER JOIN cur_test AS c
ON t.currency = c.currency AND lastdate = date_2
GROUP BY date_1;

这篇关于加入表格时，请使用最新日期作为缺失日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！