Saya perlu menemukan frekuensi urutan baris data. Saya memiliki 17.000 baris data yang mencakup hampir 120 jenis data yang berbeda dan saya perlu menemukan urutan data berapa kali diulang?

Contohnya:

a
b
c
a
b
d
a
b
c

Saya mencoba mencari urutan pengulangan beberapa orang menyebutnya urutan frekuensi. Jadi berapa kali aa dan abc dan ab dan bc dan abca dan seterusnya ada di kolom ini? Maksud saya, saya perlu menemukan berapa kali data ini memiliki grup baris yang sama.

Untuk contoh ini memiliki 4 data yang berbeda sehingga ada banyak kombinasinya. Untuk perhitungan: C(4,1)*4!+C(4,2)*2!+C(4,3)*3!+C(4,4) urutan yang berbeda dan saya perlu menghitungnya untuk setiap pesanan ada berapa kali?

Bagian singkat saya dari contoh data kolom nyata: (setiap data yang berdekatan sama dengan satu baris)

3E010000 
2010000
2010007
2010008
2010000
2010003
2010009
0201000A 
0B01000C 
2010002
3E010000 
2010000
2010007
0B010014 
2010009
0201000A 
0B01000C 
2010002

Sekarang jika Anda dapat memeriksa seluruh kolom utama ini untuk grup data ini:

3E010000 
2010000
2010007

Dan ini

3E010000 
2010000

Dan ini

2010009
0201000A 
0B01000C 
2010002

Dan seterusnya. Anda dapat melihat mereka diulang lebih dari sekali.

Baris-baris ini berulang di kolom utama pertama dan saya mencoba menemukan 1,2,3,4 dan maksimal 5 grup pesanan dari 120 jenis kombinasi data.

Saya menggunakan Microsoft SQL Server 2014. Tetapi jika tidak memungkinkan di Microsoft SQL Server, maka Anda dapat memberi saya saran atau alat lainnya. Bisakah kamu membantuku? Terima kasih banyak!

Keluaran:

0B010009 ,0B010009,0B010009,2010005,2010005,2010005     2   9

0B010014 ,0B010014,0B010014,16010002,16010002,16010002     2    3    

2010002,2010002,0201FFE0,0201FFE0       2   13    

0B0114B5 ,0B0114B5,0B0114B5,2010002,2010002,2010002,2010004,2010004,2010004    3    3    

070105B3 ,070105B3,070105B3,2010005,2010005,2010005,0201FFE1 ,0201FFE1,0201FFE1 
   3    2    

3E010000 ,3E010000,3E010000,0B010010,0B010010,0B010010 ,0B01F61D ,0B01F61D,0B01F61D     3   6    

3E010002 ,3E010002,3E010002,0B010013,0B010013,0B010013 ,0B01F80D ,0B01F80D,0B01F80D    3    3    

0B010003 ,0B010003,2010006,2010006,0B01000A ,0B01000A,2010005,2010005   4   2    

0B01FFE1 ,0B01FFE1,0B01FFE1,0B010013,0B010013,0B010013 ,0B01EAD0 ,0B01EAD0,0B01EAD0,0B010004,0B010004,0B010004     4    4     

0B01000C ,0B01000C,0B01000C,0B01FCBD,0B01FCBD,0B01FCBD ,0701FFE0 ,0701FFE0,0701FFE0,0B01000A,0B01000A,0B01000A     4    5  
2
gn khrmn 15 November 2017, 05:15

1 menjawab

Jawaban Terbaik

Kueri di bawah ini menemukan pola duplikat untuk 2, 3, 4 dan 5 baris berulang.

Ini menggunakan 'LEAD' dan 'HASHBYTES' fungsi.

Kueri bekerja dengan menghitung urutan hash untuk nilai di baris saat ini + baris berikutnya dan kemudian mengelompokkan nilai hash ini untuk menemukan pola "duplikat". Proses ini dilakukan untuk setiap baris.

Catatan: kolom urutan yang terus meningkat (untuk menunjukkan posisi baris) yaitu ID diasumsikan.

CREATE TABLE #Data( ID INT IDENTITY PRIMARY KEY, Val VARCHAR( 20 ))
INSERT INTO #Data
VALUES
( '3E010000' ), ( '2010000' ), ( '2010007' ), ( '2010008' ), ( '2010000' ),
( '2010003' ), ( '2010009' ), ( '0201000A' ), ( '0B01000C' ), ( '2010002' ),
( '3E010000' ), ( '2010000' ), ( '2010007' ), ( '0B010014' ), ( '2010009' ),
( '0201000A' ), ( '0B01000C' ), ( '2010002' )


SELECT Pat3Rows, COUNT(*) AS Cnt
FROM(
    SELECT *,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID )) AS Pat2Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID )) AS Pat3Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + LEAD( Val, 3, '' ) OVER( ORDER BY ID )) AS Pat4Rows,
        HASHBYTES( 'MD5', Val + LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + LEAD( Val, 3, '' ) OVER( ORDER BY ID ) + LEAD( Val, 4, '' ) OVER( ORDER BY ID )) AS Pat5Rows
    FROM #Data AS D1
) AS HashedGroups
GROUP BY Pat3Rows
HAVING COUNT(*) > 1

Catatan: ada kemungkinan, meskipun sangat jauh, untuk menghadapi tabrakan hash, jadi logika di atas tidak dijamin untuk menangani semua kasus yang mungkin secara teoritis. Singkatnya, saya tidak akan merekomendasikan menggunakannya jika kehidupan seseorang bergantung pada prosedur untuk selalu akurat 100%.

Anda tidak menentukan bagaimana output akan terlihat jadi saya akan menyerahkan ini kepada Anda.

Saya juga telah menguji ini di laptop saya dengan 18.000 baris dan menghasilkan hasil dalam waktu kurang dari 1 detik.

Contoh kasus penggunaan:

;WITH DataHashed AS(
SELECT *,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID )) AS Pat2Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID )) AS Pat3Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 3, '' ) OVER( ORDER BY ID )) AS Pat4Rows,
    HASHBYTES( 'MD5', Val + ',' +  LEAD( Val, 1, '' ) OVER( ORDER BY ID ) + ',' + LEAD( Val, 2, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 3, '' ) OVER( ORDER BY ID ) + ',' +  LEAD( Val, 4, '' ) OVER( ORDER BY ID )) AS Pat5Rows
FROM #Data ),
RepeatingPatterns AS(
    SELECT MIN( ID ) AS FirstRow, Pat2Rows AS PatternHash, 2 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat2Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat3Rows, 3 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat3Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat4Rows, 4 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat4Rows HAVING COUNT(*) > 1
    UNION ALL
    SELECT MIN( ID ) AS FirstRow, Pat5Rows, 5 AS PatternSize, COUNT( * ) AS Cnt FROM DataHashed GROUP BY Pat5Rows HAVING COUNT(*) > 1
)
--SELECT * FROM RepeatingPatterns
SELECT 
     CONVERT( VARCHAR( 50 ), SUBSTRING(
        ( SELECT ',' + D.Val  AS [text()]
        FROM #Data AS D
        WHERE RP.FirstRow <= D.ID AND D.ID < ( RP.FirstRow + RP.PatternSize )
        ORDER BY D.ID
        FOR XML PATH ('')
        ), 2, 1000 )) AS Pattern, CONVERT( VARCHAR( 35 ), PatternHash, 1 ) AS PatternHash, RP.PatternSize, Cnt
FROM RepeatingPatterns AS RP

Contoh keluaran:

Pattern                                            PatternHash                         PatternSize Cnt
-------------------------------------------------- ----------------------------------- ----------- -----------
0201000A,0B01000C                                  0x499D8B1750A9BF57795B4D60D58DCF81  2           2
2010000,2010007                                    0x7EDE1E675D934F3035DACAC53F74DD14  2           2
3E010000,2010000                                   0x85FBFD817CFBB9BD08E983671EB594B7  2           2
2010009,0201000A                                   0x8E18E36B989BD859AF039238711A7F8C  2           2
0B01000C,2010002                                   0xF1EABB115FB3AEF2D162FB3EC7B6AFDA  2           2
0201000A,0B01000C,2010002                          0x6DE203B38A13501881610133C1EDBF85  3           2
2010009,0201000A,0B01000C                          0x9EB3ACFE8580A39FC530C7CA54830602  3           2
3E010000,2010000,2010007                           0xE414661F54C985B7ED9FA82FF05C1219  3           2
2010009,0201000A,0B01000C,2010002                  0x7FCDB748E37A6F6299AE8B269A4B0E49  4           2
1
Alex 15 November 2017, 21:24