如何使用标准SQL在GBQ中更新带有新行的Struck。

huangapple
go评论46阅读模式
英文:

How to Update a Struck with New Rows in GBQ with Standard SQL

问题

以下是代码部分的翻译：

我有以下表格：

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2022-10-13' AS DATE) AS session_date,
    'denied' AS value,
  UNION ALL
  SELECT
    1,
    CAST('2022-10-15' AS DATE),
    'granted'
  UNION ALL
  SELECT
    1,
    CAST('2022-10-18' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-01' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-05' AS DATE),
    'granted'
  UNION ALL
  SELECT
    3,
    CAST('2022-01-01' AS DATE),
    'granted'
  UNION ALL
  SELECT
    4,
    CAST('2022-01-03' AS DATE),
    'granted'),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date,
      value)
  ORDER BY
    session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

它看起来像这样：

[![进入图像描述][1]][1]

现在，我有另一个表格，为客户1和2创建新记录，就像您在图片中看到的那样：

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2023-05-08' AS DATE) AS session_date,
    'denied' AS value,
  UNION ALL
  SELECT
    2,
    CAST('2023-05-08' AS DATE),
    'granted'
  ),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date,
      value)
  ORDER BY
    session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

[![进入图像描述][2]][2]

我想知道是否有办法将这些结果附加到第一个表格中的struct，就像这样：

[![进入图像描述][3]][3]

我尝试了INSERT，但它在表格中创建了新记录，而不是将它们附加到struct中。

我看了UPDATE，但我在文档中没有看到如何在STRUCT中使用它，只有在ARRAY中有说明。这是我尝试过的：

UPDATE
  `table1`
SET
  push_permission = ARRAY(
  SELECT
    push_permission
  FROM
    UNNEST(push_permission) AS push_permission
  UNION ALL
  SELECT
    (CAST(CURRENT_DATE()-1 AS DATE),
      push_permission.push_system_permission  ))
  WHERE client_id IN (SELECT
    DISTINCT(client_id)
  FROM
    `table2`)

在GBQ中是否有办法做到这一点？

希望这可以帮助您理解代码部分的内容。如果您有其他问题或需要进一步的翻译，请告诉我。

英文:

I have the following table:

WITH
source_data AS (
SELECT
1 AS client_id,
CAST('2022-10-13' AS DATE) AS session_date,
'denied' AS value,
UNION ALL
SELECT
1,
CAST('2022-10-15' AS DATE),
'granted'
UNION ALL
SELECT
1,
CAST('2022-10-18' AS DATE),
'denied'
UNION ALL
SELECT
2,
CAST('2022-01-01' AS DATE),
'denied'
UNION ALL
SELECT
2,
CAST('2022-01-05' AS DATE),
'granted'
UNION ALL
SELECT
3,
CAST('2022-01-01' AS DATE),
'granted'
UNION ALL
SELECT
4,
CAST('2022-01-03' AS DATE),
'granted'),
max_date AS (
SELECT
client_id,
session_date,
value
FROM
source_data )
SELECT
client_id,
MAX(session_date) AS last_activity,
ARRAY_AGG(STRUCT(session_date,
value)
ORDER BY
session_date) AS push_permission
FROM
max_date
GROUP BY
1

It looks like this:

如何使用标准SQL在GBQ中更新带有新行的Struck。

Now, I have another table that create new records for client 1 and 2, like you see in the picture:

WITH
source_data AS (
SELECT
1 AS client_id,
CAST('2023-05-08' AS DATE) AS session_date,
'denied' AS value,
UNION ALL
SELECT
2,
CAST('2023-05-08' AS DATE),
'granted'
),
max_date AS (
SELECT
client_id,
session_date,
value
FROM
source_data )
SELECT
client_id,
MAX(session_date) AS last_activity,
ARRAY_AGG(STRUCT(session_date,
value)
ORDER BY
session_date) AS push_permission
FROM
max_date
GROUP BY
1

如何使用标准SQL在GBQ中更新带有新行的Struck。

I would like to know if there is any way to append these results inside the struct in the following way in the first table, like this:

如何使用标准SQL在GBQ中更新带有新行的Struck。

I have tried with INSERT, but it is creating new records in the table, not appending them to the struct.

I have take a look to UPDATE, but I don't see in the documentation how to use it in STRUCT, only in ARRAY. This is what I tried:

UPDATE
`table1`
SET
push_permission = ARRAY(
SELECT
push_permission
FROM
UNNEST(push_permission) AS push_permission
UNION ALL
SELECT
(CAST(CURRENT_DATE()-1 AS DATE),
push_permission.push_system_permission ))
WHERE client_id IN (SELECT
DISTINCT(client_id)
FROM
`table2`)

Is there any way to do this in GBQ?

答案1

得分: 2

使用以下方法可能会有所帮助。
（你可以用以下查询的结果替换现有表）

```sql
WITH first_source_data AS (
-- 在此处放入你的第一个查询
),
second_source_data AS (
-- 在此处放入你的第二个查询
)
SELECT client_id,
       MAX(last_activity) last_activity,
       ARRAY_CONCAT_AGG(push_permission) push_permission
  FROM (
    SELECT * FROM first_source_data
     UNION ALL
    SELECT * FROM second_source_data
  ) GROUP BY 1;

查询结果

如何使用标准SQL在GBQ中更新带有新行的Struck。

另请参阅


<details>
<summary>英文:</summary>
You might consider below approach.  
(you can replace the existing table with the result of below query)
```sql
WITH first_source_data AS (
-- put your first query here
),
second_source_data AS (
-- put your second query here
)
SELECT client_id,
MAX(last_activity) last_activity,
ARRAY_CONCAT_AGG(push_permission) push_permission
FROM (
SELECT * FROM first_source_data
UNION ALL
SELECT * FROM second_source_data
) GROUP BY 1;

Query result

如何使用标准SQL在GBQ中更新带有新行的Struck。

See also

huangapple
  • 本文由 发表于 2023年5月22日 21:03:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76306493.html
  • google-bigquery
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定