多个case语句/多个Update语句

Question

您能告诉我如何将CASE语句与同一列上的多个CASE一起使用吗？

update FRIDAY.Joined_table
SET
Cost = CASE WHEN Channel = 'SEA' THEN Paid_Costs ELSE Cost END,
Clicks = CASE WHEN Channel = 'SEA' THEN Paid_Clicks ELSE Clicks END,
impressions = CASE WHEN Channel = 'SEA' THEN Paid_Impressions ELSE Impressions END,
Cost = CASE WHEN Channel = 'Performance-Display' THEN DBM_Costs ELSE cost END
Where New_Ids IS NOT NULL

“这里它告诉我:在6:1多次更新分配的物料成本。

Answer 1

你可以试试下面的-

update FRIDAY.Joined_table
SET
Cost = CASE WHEN Channel = 'SEA' THEN Paid_Costs 
WHEN Channel = 'Performance-Display' THEN DBM_Costs ELSE Cost END,
Clicks = CASE WHEN Channel = 'SEA' THEN Paid_Clicks ELSE Clicks END,
impressions = CASE WHEN Channel = 'SEA' THEN Paid_Impressions ELSE Impressions END
Where New_Ids IS NOT NULL

Answer 2

对于BigQuery标准SQL

下面是另一个选项，它消除了表达CASE语句时的冗余，并且看起来更干净、更容易管理

#standardSQL
UPDATE FRIDAY.Joined_table t1
SET 
  cost = x, 
  clicks = y, 
  impressions = z
FROM FRIDAY.Joined_table t2,
UNNEST([
  CASE channel 
    WHEN 'SEA' THEN STRUCT(paid_costs AS x, paid_clicks AS y, paid_impressions AS z)
    WHEN 'Performance-Display' THEN STRUCT(dbm_costs AS x, clicks AS y, impressions AS z)
    ELSE STRUCT(cost AS x, clicks AS y, impressions AS z)
  END 
  ]) val
WHERE TO_JSON_STRING(t1) = TO_JSON_STRING(t2)    

没有什么东西是零价格的-所以，这里有self join :o(

为了解决这个问题-下面是另一个选择

#standardSQL
CREATE TEMP FUNCTION CASE_UPDATE(t ANY TYPE) AS (
  CASE t.channel 
    WHEN 'SEA' THEN STRUCT(t.paid_costs AS x, t.paid_clicks AS y, t.paid_impressions AS z)
    WHEN 'Performance-Display' THEN STRUCT(t.dbm_costs AS x, t.clicks AS y, t.impressions AS z)
    ELSE STRUCT(t.cost AS x, t.clicks AS y, t.impressions AS z)
  END  
);
UPDATE FRIDAY.Joined_table t 
SET 
  cost = CASE_UPDATE(t).x, 
  clicks = CASE_UPDATE(t).y, 
  impressions = CASE_UPDATE(t).z
WHERE TRUE

为了进一步简化，可以最终将上面的代码写成(假设您知道用于更新列的类型，如INT64或FLOAT64 / NUMERIC等)。

#standardSQL
CREATE TEMP FUNCTION CASE_UPDATE(t ANY TYPE) 
RETURNS STRUCT<cost FLOAT64, clicks INT64, impressions INT64>
AS (
  CASE t.channel 
    WHEN 'SEA' THEN STRUCT(t.paid_costs, t.paid_clicks, t.paid_impressions)
    WHEN 'Performance-Display' THEN STRUCT(t.dbm_costs, t.clicks, t.impressions)
    ELSE STRUCT(t.cost, t.clicks, t.impressions)
  END  
);
UPDATE FRIDAY.Joined_table t
SET 
  cost = CASE_UPDATE(t).cost, 
  clicks = CASE_UPDATE(t).clicks, 
  impressions = CASE_UPDATE(t).impressions
WHERE TRUE