时间戳

首页 > 解决方案 > BigQuery 多行在单个作业中更新

node.js - BigQuery 多行在单个作业中更新

问题描述

我有两个数组，一个用于我要更新的值，一个用于 where 语句。这是用户名列表：

[
  '4feathersandfur',       '4flyman',
  '4iisx',                 '4lergico_',
  '4mygal',                '4mygal',
  '4mygal',                '4mygal',
  '4mygal',                '4mygal',
  '4mygal',                '4ringzryan',
  '4summeror4ever',        '5.by',
  '5.by',                  '5.by',
  '5.by',                  '5.by',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '5.vt',                  '5.vt',
  '501_f',                 '502_nesha',
  '5050_therealvito',      '50_shades_of_dramatic',
  '50_shades_of_dramatic', '50tonsdesono_',
  '526ha',                 '5588fatima',
  '5588fatima',            '559ac',
  '55sommy55',             '55sommy55',
  '58_q8',                 '5_alsheh7i.94'
]

这是 new_ig_followers_count 的列表

[
  '1001', '1000', '1001', '1000',
  '1000', '1000', '1000', '1000',
  '1000', '1000', '1000', '1001',
  '1001', '1000', '1000', '1000',
  '1000', '1000', '1001', '1001',
  '1001', '1001', '1001', '1001',
  '1001', '1001', '1000', '1000',
  '1001', '1001', '1001', '1001',
  '1001', '1001', '1001', '1000',
  '1001', '1001', '1000', '1001'
]

我想要一项工作来使用这些用户名及其相应的追随者人数更新所有行，是否可以在一项工作中完成。

表的架构是

Field name  Type    Mode    Description
username    STRING  NULLABLE    
website STRING  NULLABLE    
description STRING  NULLABLE    
url STRING  NULLABLE    
followed_by INTEGER NULLABLE    
email   STRING  NULLABLE    
new_ig_followers_count  INTEGER NULLABLE    
updated_at  TIMESTAMP   NULLABLE    
IG_alive    BOOLEAN NULLABLE

标签： node.jsgoogle-bigquerygcloud

解决方案

以下是 BigQuery 标准 SQL 

UPDATE `instagramdata.instagram.40_50_followers`
SET 
  new_ig_followers_count = followersCount, 
  updated_at = CURRENT_TIMESTAMP(), 
  IG_alive = TRUE
FROM (
  SELECT [
    '4feathersandfur',       '4flyman',
    '4iisx',                 '4lergico_',
    '4mygal',                '4mygal',
    '4mygal',                '4mygal',
    '4mygal',                '4mygal',
    '4mygal',                '4ringzryan'
  ] AS usernameArray,
  [
    '1001', '1000', '1001', '1000',
    '1000', '1000', '1000', '1000',
    '1000', '1000', '1000', '1001'
  ] AS followersCountArray
),
UNNEST(usernameArray) AS Name WITH OFFSET
JOIN UNNEST(followersCountArray) AS followersCount WITH OFFSET
USING(OFFSET)
WHERE username = Name

推荐阅读