sql-update - 如何在不更改表架构的情况下将查询结果存储在当前表上？

Question

我有一个结构

  {
    id: "123",
    scans:[{
       "scanid":"123",
       "status":"sleep"
      }]
  },
  {
    id: "123",
    scans:[{
       "scanid":"123",
       "status":"sleep"
      }]
  }

删除重复的查询:

      SELECT *
    FROM (
      SELECT
          *,
          ROW_NUMBER()
              OVER (PARTITION BY id)
              row_number,
      FROM table1
    )
    WHERE row_number = 1

我将目标表指定为 table1。

在这里，我将扫描作为重复记录，将 scanid 作为字符串，将状态作为字符串。但是当我进行一些查询(我正在查询以删除重复项)并覆盖现有表时，表架构发生了变化。变成 scans_scanid(string)和 scans_status(string) .扫描记录架构现已更改。请建议我哪里出错了？

Answer 1

众所周知，NEST() 与 UnFlatten 结果输出不兼容，主要用于子查询中的中间结果。

尝试以下解决方法
注意，我使用 INTEGER 作为 id 和 scanid。如果它们应该是 STRING 你需要
一种。在输出架构部分进行更改
也
湾删除 t = {scanid:parseInt(x[0]), status:x[1]} 中 parseInt() 函数的使用

SELECT id, scans.scanid, scans.status 
FROM JS(
  (      // input table
    SELECT id, NEST(CONCAT(STRING(scanid), ',', STRING(status))) AS scans
    FROM (
      SELECT id, scans.scanid, scans.status 
      FROM (
        SELECT id, scans.scanid, scans.status, 
               ROW_NUMBER() OVER (PARTITION BY id) AS dup
        FROM table1
      ) WHERE dup = 1  
    ) GROUP BY id
  ),
  id, scans,     // input columns
  "[{'name': 'id', 'type': 'INTEGER'},    // output schema
    {'name': 'scans', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'scanid', 'type': 'INTEGER'},
       {'name': 'status', 'type': 'STRING'}
     ]    
    }
  ]",
  "function(row, emit){    // function 
    var c = [];
    for (var i = 0; i < row.scans.length; i++) {
      x = row.scans[i].toString().split(',');
      t = {scanid:parseInt(x[0]), status:x[1]}
      c.push(t);
    };
    emit({id: row.id, scans: c});  
  }"
)

这里我使用 BigQuery User-Defined Functions .他们非常强大，但仍有一些 Limits和 Limitations要知道。还请记住 - 他们非常适合被视为昂贵的候选人 High-Compute queries

Complex queries can consume extraordinarily large computing resources relative to the number of bytes processed. Typically, such queries contain a very large number of JOIN or CROSS JOIN clauses or complex User-defined Functions.