如何使用分区(或其他方法)连接两个表

g0czyy6m  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(261)

我有两张table:
表a包含销售产品(test1、test2)和每日信息
表b显示了这些产品的类别变化历史(产品可以每年或每天改变一次类别,具体取决于产品)
我想把当前产品类别的销售记录保留到今天(如果今天类别发生了变化,我想把它保留到今天和以后,在下一次变化之前)
请在没有太多子查询的情况下提供帮助(表很重)

TABLE A >>
        uid  name  date
        1   test1  2019-09-01
        2   test1  2019-09-02
        3   test1  2019-09-03
        4   test2  2019-09-01
        5   test2  2019-09-02
        6   test2  2019-09-03

    TABLE B >>
    uid  name  date_change  category
    1   test1  2019-08-30  cat1
    2   test1  2019-09-02  cat2
    4   test1  2019-09-04  cat3
    5   test2  2019-09-01  cat4
    6   test2  2019-09-03  cat5

    TABLE Result >>
    uid  name  date       date_change  category
    1   test1  2019-09-01  2019-08-30  cat1
    2   test1  2019-09-02  2019-09-02  cat2
    3   test1  2019-09-03  2019-09-02  cat2
    4   test2  2019-09-01  2019-09-01  cat4
    5   test2  2019-09-02  2019-09-01  cat4
    6   test2  2019-09-03  2019-09-03  cat5
ui7jx7zq

ui7jx7zq1#

下面是bigquery标准sql


# standardSQL

SELECT a.name, `date`, date_change, category
FROM `project.dataset.tableA` a
LEFT JOIN(
  SELECT name, date_change, category, 
    DATE_SUB(LEAD(date_change, 1, DATE '2100-01-01') OVER(PARTITION BY name ORDER BY date_change), INTERVAL 1 DAY) last_day
  FROM `project.dataset.tableB` 
) b
ON a.name = b.name 
AND a.date BETWEEN b.date_change AND b.last_day
-- ORDER BY name, date_change

如果要应用到问题的样本数据-结果是

Row name    date        date_change category     
1   test1   2019-09-01  2019-08-30  cat1     
2   test1   2019-09-02  2019-09-02  cat2     
3   test1   2019-09-03  2019-09-02  cat2     
4   test2   2019-09-01  2019-09-01  cat4     
5   test2   2019-09-02  2019-09-01  cat4     
6   test2   2019-09-03  2019-09-03  cat5
0h4hbjxa

0h4hbjxa2#

一个选项使用相关子查询来检索最新的 catbdatea :

select 
    a.*,
    (
        select cat 
        from b 
        where b.name = a.name and b.date_changed <= a.date 
        order by b.date_changed desc 
        limit 1
    ) cat
from a

相关问题