さまざまなデータソースの顧客のテーブルがあります。SSN、License#、およびいくつかの一意のIDがありますが、すべてのソースが同じIDを持っているわけではありません。ID列(SSN、ライセンス、SystemID)のレコードを比較し、同じ人物が見つかった場合はマップされたIDを割り当てたいと思います。
CTEは使用できると思いますが、どこから始めればよいかわかりません。まだSQLで自分のやり方を学ぼうとしています。どんな助けでもありがたいです。ありがとう。
テーブルの外観は次のとおりです。
Source|RowID|SSN |License|SystemID
A |1 |SSN1|Lic111 |
A |2 | | |Sys666
B |3 |SSN2| |Sys777
C |4 |SSN1| |
D |5 | |Lic333 |
D |6 | |Lic333 |Sys666
E |7 | | |Sys777
結果(MapCustomerIDを追加)
Source|RowID|SSN |License|SystemID|MapCustomerID
A |1 |SSN1|Lic111 | |1
A |2 | | |Sys666 |2
B |3 |SSN2| |Sys777 |3
C |4 |SSN1| | |1
D |5 | |Lic999 | |4
D |6 | |Lic333 |Sys666 |2
E |7 | | |Sys777 |3
これが、問題に対する「十分な」アプローチである可能性があるものです。
3つのディメンションのそれぞれに沿って、そのディメンションの最小行IDを見つけます(NULLの特別な処理を使用)。その場合、全体的な顧客IDは、これら3つのIDの最小値になります。ギャップなしでシーケンシャルにするには、を使用しますdense_rank()
。
with ids as (
select t.*,
(case when SSN is not null
then min(RowId) over (partition by SSN)
end) as SSN_id,
(case when License is not null
then min(RowId) over (partition by License)
end) as License_id,
(case when SystemId is not null
then min(RowId) over (partition by SystemId)
end)as SystemId_id
from t
),
leastid as (
select ids.*,
(case when SSN_Id <= coalesce(License_Id, SSN_Id) and
SSN_Id <= coalesce(SystemId_id, SSN_Id)
then SSN_Id
when License_Id <= coalesce(SystemId_id, License_Id)
then License_Id
else SystemId_id
end) as LeastId
from ids
)
select Source, RowID, SSN, License, SystemID,
dense_rank(LeastId) over (order by LeastId) as MapCustomerId
from LeastIds;
これは完全なソリューションではありませんが、データに対しては機能します。次の場合は機能しません。
A |1 |SSN1|Lic111 | |1
A |2 |SSN1| |Sys666 |2
A |3 | | |Sys666 |2
これには2つの「ホップ」が必要だからです。
過去にこの状況に直面したとき、テーブルに追加の列を作成update
し、さまざまなディメンションで最小IDを取得するために繰り返し使用しました。このような反復により、さまざまな部分がすばやく接続されます。同じことを行うために再帰CTEを作成することはおそらく可能です。ただし、上記のより簡単な解決策で問題が解決する場合があります。
編集:
以前にこの問題に直面したことがあるので、(更新を繰り返すのではなく)単一のクエリソリューションを考え出したかったのです。これは、再帰的なCTEを使用して可能です。動作しているように見えるコードは次のとおりです。
with t as (
select 'A' as source, 1 as RowId, 'SSN1' as SSN, 'Lic111' as License, 'ABC' as SystemId union all
select 'A', 2, 'SSN1', NULL, 'Sys666' union all
select 'A', 3, NULL, NULL, 'Sys666' union all
select 'A', 4, NULL, 'Lic222', 'Sys666' union all
select 'A', 5, NULL, 'Lic222', NULL union all
select 'A', 6, NULL, 'Lic444', NULL
),
first as (
select t.*,
(select min(RowId)
from t t2
where t2.SSN = t.SSN or
t2.License = t.License or
t2.SystemId = t.SystemId
) as minrowid
from t
),
cte as (
select rowid, minrowid
from first
union all
select cte.rowid, first.minrowid
from cte join
first
on cte.minrowid = first.rowid and
cte.minrowid > first.minrowid
),
lookup as (
select rowid, min(minrowid) as minrowid,
dense_rank() over (order by min(minrowid)) as MapCustomerId
from cte
group by rowid
)
select t.*, lookup.MapCustomerId
from t join
lookup
on t.rowid = lookup.rowid;
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加