SnowflakeSQL-在變數VARCHAR范圍內查找缺失值，具有多個表連接

好的，所以我已經為其他人的查詢做了很多挖掘和嘗試各種片段和解決方案，但我似乎無法在這里獲得任何牽引力。另外，兩周前才開始嘗試sql，所以請原諒初學者......

我有一個帶有 ITEM_ID 和 GROUP_ID 的 ITEMS 表，以及帶有 GROUP_ID 和 GROUP_NAME 的 GRP 表

有時在最小值和最大值內缺少 ITEM_ID（不幸的是，并非所有組都從 1 開始）并且它們是 VARCHAR

我希望能夠按 GROUP_NAME 搜索缺少的 ITEM_ID

我一直在嘗試使用：

with nums as (
SELECT 
seq4() 1 as id
  from table(generator(rowcount => 10)) 
)

SELECT nums.id

要獲得 1 到多行的串列，但需要這是 ITEM_ID 的最小值和最大值。我試圖輸入一個 CAST 甚至只是一個 min(item.ITEM_ID) 但我遇到了問題，因為 ITEMS 表已經很大，因此按 GROUP_NAME 排序

很抱歉，我沒有很多代碼示例，因為我大多無法運行任何東西。但這是我加入 ITEMS 和 GRP 表以按名稱搜索的方式：

SELECT
    item.NAME, item.ITEM_ID, grp.NAME
FROM
    API_ITEMS item
    LEFT JOIN API_GROUPS grp ON item.group_id = grp.id
WHERE
    grp.NAME = 'silverware'
GROUP by item.ITEM_ID ASC

我認為很多這只是因為我對 sql 非常熟悉，但基本上我正在嘗試搜索專案的 GROUP_NAME 以找到丟失的 ITEM_IDS

在我的 API_ITEMS 表中，我有所有組的所有專案，我想僅按 grp.NAME 進行過濾，并找出 min(item.ITEM_ID)、max(item.ITEM_ID) 中缺少哪些 ITEM_ID。不確定我是否需要將最小值/最大值轉換為 INT，或者 varchar 在這里是否同樣有效。我在想如果我可以將它加入到從 min -> max 的 seq4() 中，我可以在有 seq4() 條目但沒有 ITEM_ID 條目的地方找到空條目

1 這方面的例子是我有一個連接表

ITEM_ID   grp.NAME
1         silverware
2         silverware
3         silverware
4         silverware
5000      silverware

我找到了一個間隙腳本，它將輸出間隙從 5 開始，以 4999 結束，但這并不能用于我的所有 grp.NAME

在這個例子中，我的想法是我可以創建一個 seq4() 作為 5000 個專案的 nums 表。在 item.ITEM_ID = nums.ID 上加入專案表以獲得

nums.ID      ITEM_ID
1            1
2            2
3            3
4            4
5            null
6            null
...
5000         5000

我想要一個輸出，其中缺少每個 ITEM.ID，例如

MISSING_ITEM_ID
5
6
7
...
4999

感謝任何愿意接受這個，握住我的手，幫助我的人。

我在雪花上，我無法訪問 Tally 表...

uj5u.com熱心網友回復：

因此，按照您的示例，我制作了一些 CTE（可能是表格，但它們是可互換的），選擇了較小的數字范圍，因此輸出不會太長

with items(item_id, item_name, group_id ) as (
    select * from values
    (1, 'item1', 100),
    (2, 'item2', 100),
    (3, 'item3', 100),
    (4, 'item4', 100),
    (9, 'item31', 100),
    (1, 'item1-101', 101)
), groups(group_id, name) as (
    select * from values
    (100, 'silverware'),
    (101, 'leadware')
)

并加入那些尋找“銀器”專案，就像你有

select i.item_id,
    i.item_name,
    g.name
from items as i
join groups as g
    on i.group_id = g.group_id
where g.name = 'silverware'    

給出：

現在你想要一個具有一系列數字的 CTE，為此你必須使用 ROW_NUMBER 函式，因為 SEQx() 可以在輸出中有間隙，如果你只想要向上的不同數字 SEQ 更快，但如果你想沒有孔，SEQ會隨機燒死你。

因此，讓我們將 CTE 的數字交換為 ROW_NUMBER，并選擇一個比您將擁有的任何范圍都大的大數字。在現實世界中，我會制作一個包含 100 萬行的表格，然后使用它。

with nums as (
    SELECT 
        row_number()over(order by null) as id
    from table(generator(rowcount => 5000)) 
)

所以現在我們有大量的 idnums和我們想要的資料。

• 所以現在我們必須要做的事情，將范圍加入我們的資料，并修剪超出范圍的值..
• 找到差距

所以有了這些：

with nums as (
    SELECT 
        row_number()over(order by null) as id
    from table(generator(rowcount => 5000)) 
), wanted_data as (
    select i.item_id,
        i.item_name,
        i.group_id,
        g.name
    from items as i
    join groups as g
        on i.group_id = g.group_id
    where g.name = 'silverware'   
), ranges as (
    select group_id,
        min(item_id) as min_id,
        max(item_id) as max_id
    from wanted_data
    group by 1
)

這顯示了范圍連接和左連接的前半部分

select 
    n.id,
    r.group_id,
    w.*
from nums as n
join ranges as r
    on n.id between r.min_id and r.max_id
left join wanted_data as w
    on w.item_id = n.id

現在我們可以通過添加到末尾來過濾掉丟失的左連接：

where w.item_id is null

所以我們不需要選擇那些失敗的加入w.*值，但我們目前缺少組名。此時您可以重新加入組表（這是我傾向于在大型作業流程中做事的方式，以避免在轉換階段攜帶過多的值）。或者只是隨身攜帶。

因此所有的SQL在一起：

with items(item_id, item_name, group_id ) as (
    select * from values
    (1, 'item1', 100),
    (2, 'item2', 100),
    (3, 'item3', 100),
    (4, 'item4', 100),
    (9, 'item31', 100),
    (1, 'item1-101', 101)
), groups(group_id, name) as (
    select * from values
    (100, 'silverware'),
    (101, 'leadware')
), nums as (
    SELECT 
        row_number()over(order by null) as id
    from table(generator(rowcount => 5000)) 
), wanted_data as (
    select i.item_id,
        i.item_name,
        i.group_id,
        g.name
    from items as i
    join groups as g
        on i.group_id = g.group_id
    where g.name = 'silverware'   
), ranges as (
    select group_id,
        name as group_name,
        min(item_id) as min_id,
        max(item_id) as max_id
    from wanted_data
    group by 1,2
)
select 
    n.id,
    r.group_id,
    r.group_name
from nums as n
join ranges as r
    on n.id between r.min_id and r.max_id
left join wanted_data as w
    on w.item_id = n.id
where w.item_id is null

給出：

另一種范圍方式：

現在，如果您的 id 的數字空間非常高，那么擁有一個巨大的 nums 表可能會很嚴重。

with items(item_id, item_name, group_id ) as (
    select * from values
    (1000001, 'item1', 100),
    (1000002, 'item2', 100),
    (1000003, 'item3', 100),
    (1000004, 'item4', 100),
    (1000009, 'item31', 100),
    (2000001, 'item1-101', 101)
)

然后更改為 nums 以生成 0

), nums as (
    SELECT 
        row_number()over(order by null)-1 as id
    from table(generator(rowcount => 5000)) 
)

您可以使用較小的范圍表，并且只使用相對數學：

select 
    n.id r.min_id as id,
    r.group_id,
    r.group_name
from nums as n
join ranges as r
    on n.id <= r.span
left join wanted_data as w
    on w.item_id = n.id r.min_id
where w.item_id is null

要得到：

uj5u.com熱心網友回復：

不確定這是否正是您要尋找的，但您可以使用 uniform 函式為隨機函式提供 Min 和 Max 閾值。在此示例中，對于創建的 10 行中的每一行，您將獲得 1 的 MIN 和 10 的 MAX。

 SELECT
   uniform(1, 10, random(1)) as ITEM_ID
from table(generator(rowcount=>10));

統一檔案： https ://docs.snowflake.com/en/sql-reference/functions/uniform.html

標籤：sql 加入 雪花云数据平台

上一篇：Pandas將串列列轉換為文本列資料預處理

下一篇：運行SQL查詢UPDATE陳述句以回圈遍歷結果集中的每一行并更新列的Python腳本