根据两个条件联接两个表"cannot partition on repeated field"

Question

我正在使用BigQuery。

我有一个子查询，它从具有account_id、product、date和product_spend字段的表中提取数据。此子查询通过将每个行项目相加来计算每个"account_id"的每个产品的总使用寿命。

SELECT  account_id,
        product,
        SUM(product_spend)/1000000 lifetime_product_spend
FROM    usage
GROUP BY 1, 2

结果如下：

table: lifetime                        
account_id         product          lifetime_product_spend                
===========================================================         
    A              product1              50
    A              product2              20   
    B              product2              100
    B              product3              150
    C              product3              500

我试图保留值，并用一个更大的查询将它们连接起来：

SELECT  account_id,
        product,
        month,
        SUM(spend)
FROM data_source
WHERE month >= DATE_ADD(today ,-5,"MONTH")
GROUP BY 1, 2, 3

此查询的表如下所示：

table: monthly                        
account_id         product              month            spend             
=================================================================
    A              product1              1                10
    A              product1              2                20
    A              product1              3                30
    A              product2              1                5
    A              product2              2                15
    B              product2              2                100
    B              product3              2                100
    B              product3              3                50
    C              product3              1                100
    C              product3              2                400

我不是在第二个表中使用聚合来计算lifetime_product_spend。由于数据量太大，我只能包括过去6个月的数据。这就是为什么我要在不同的表中计算一生的花费并加入它们。

我当前的查询失败：

SELECT  d.account_id,
        d.product,
        d.month,
        sum(d.spend),
        u.lifetime_product_spend
FROM data_source d
LEFT JOIN (SELECT  account_id,
           product,
           SUM(product_spend)/1000000 lifetime_product_spend
           FROM usage
           GROUP BY account_id, product) u
ON d.account_id = u.account_id
WHERE d.month >= DATE_ADD(today ,-5,"MONTH")
GROUP BY d.account_id, d.product, d.month, u.lifetime_product_spend

因为它似乎没有像lifetime表中那样为每个产品分配lifetime数字。那是因为我只在account_id上加入。请参阅下面的错误输出。我截断了这个表，因为它基本上添加了我在lifetime_product_spend（5）中的输出数量，并为每个月、产品和公司添加了一个。。。因为它忽略了这些值的"产品"分配：

table: monthly                        
account_id         product           month         spend      lifetime_product_spend       
=====================================================================================
    A              product1           1             10                   50
    A              product1           1             10                   20
    A              product1           1             10                   100
    A              product1           1             10                   150
    A              product1           1             10                   500
    A              product1           2             20                   50
    A              product1           2             20                   20
    A              product1           2             20                   100
    A              product1           2             20                   150
    A              product1           2             20                   500

有办法让我加入他们两个吗？我试着在x=x和y=y=上加入

SELECT  d.account_id,
        d.product,
        d.month,
        sum(d.spend),
        u.lifetime_product_spend
FROM data_source d
LEFT JOIN (SELECT  account_id,
           product,
           SUM(product_spend)/1000000 lifetime_product_spend
           FROM usage
           GROUP BY account_id, product) u
ON (d.account_id = u.account_id AND d.product = u.product)
WHERE d.month >= DATE_ADD(today ,-5,"MONTH")
GROUP BY d.account_id, d.product, d.month, u.lifetime_product_spend

但它给了我一个错误："执行失败错误：无法对重复字段d.product进行分区"。我希望我的最后一张桌子看起来像这样：

table: monthly                        
account_id         product           month         spend      lifetime_product_spend       
=====================================================================================
    A              product1           1             10                   50
    A              product1           2             20                   50
    A              product1           3             30                   50
    A              product2           1             5                    20
    A              product2           2             15                   20
    B              product2           2             100                  100
    B              product3           2             100                  150
    B              product3           3             50                   150
    C              product3           1             100                  500
    C              product3           2             400                  500

我想我需要"FLATTEN"在某个地方，但我似乎找不到合适的地方。感谢阅读。

Answer 1

SELECT  d.account_id,
        d.product,
        d.month,
        sum(d.spend),
        u.lifetime_product_spend
FROM FLATTEN(data_source, product) d
LEFT JOIN (SELECT  account_id,
           product,
           SUM(product_spend)/1000000 lifetime_product_spend
           FROM usage
           GROUP BY account_id, product) u
ON (d.account_id = u.account_id AND d.product = u.product)
WHERE d.month >= DATE_ADD(today ,-5,"MONTH")
GROUP BY d.account_id, d.product, d.month, u.lifetime_product_spend

以上内容适用于围绕重复字段d.product展开的原始数据源。感谢您的评论和帮助。

Answer 2

写入"Select…from usage"作为子查询，并在data_source表上应用INNER JOIN或LEFT JOIN。

SELECT  d.account_id,
        d.product,
        d.month,
    sum(d.spend),
    u.lifetime_product_spend
from data_source d
 left join (SELECT  account_id,
        product,
        SUM(product_spend)/1000000 lifetime_product_spend
         FROM usage
          GROUP BY account_id, product) u
  on(d.account_id=u.account_id and d.product=u.product)
WHERE d.month >= DATE_ADD(today ,-5,"MONTH")
GROUP BY d.account_id, d.product, d.month, u.lifetime_product_spend