大查询|识别重复的行后出现嵌套 [英] Big Query | Unnest after Identifying Duplicate Rows

问题描述

我正在尝试取消表格中的line_items的嵌套.但是，我必须从表中删除重复项，这使正常情况下无法正常工作.

I'm trying to unnest the line_items in my table. However, I have to remove duplicates from our table and it's throwing off what should normally work.

这是我用来消除重复项的查询:

This is the query I use to eliminate duplicates:

   SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
       FROM `shopify.orders` )
      -- identify duplicate rows
   where instance = 1

但是，当我尝试取消此查询中的line_items嵌套时，它不再能够按ID进行分区

However, when I try to unnest the line_items in this query, it's no longer able to partition by id

   SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
       FROM `shopify.orders`, unnest(line_items) as items )
      -- identify duplicate rows
   where instance = 1

有什么想法可以消除重复项，同时又取消line_items的构想吗?

Any ideas how I can eliminate duplicates while also unnesting the line_items?

在不删除重复项的情况下，我的数据如下所示:

Without removing duplicates, this is what my data looks like:

删除重复项时，其外观如下:

When removing duplicates, this is what it looks like:

推荐答案

您可以先执行dedup，然后执行嵌套结果-如下例所示

You can first do dedup and then unnest result - something as in below example

SELECT * EXCEPT(instance, line_items) FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `shopify.orders` 
), UNNEST(line_items) as item
   -- identify duplicate rows
WHERE instance = 1

这篇关于大查询|识别重复的行后出现嵌套的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！