JOIN (Azure Stream Analytics)

Seperti T-SQL standar, JOIN dalam bahasa kueri Azure Stream Analytics digunakan untuk menggabungkan rekaman dari dua sumber input atau lebih. JOIN di Azure Stream Analytics bersifat temporal, yang berarti bahwa setiap JOIN harus memberikan beberapa batasan tentang seberapa jauh baris yang cocok dapat dipisahkan tepat waktu. Misalnya, mengatakan "bergabung dengan peristiwa TollBoothEntry dengan peristiwa TollBoothExit ketika terjadi pada LicensePlate dan TollId yang sama dan dalam waktu 5 menit satu sama lain" adalah sah; tetapi "bergabung dengan peristiwa TollBoothEntry dengan peristiwa TollBoothExit ketika terjadi pada LicensePlate dan TollId" tidak - ini akan cocok dengan setiap TollBoothEntry dengan koleksi yang tidak terbatas dan berpotensi tak terbatas dari semua TollBoothExit dengan LicensePlate dan TollId yang sama.

Batas waktu untuk hubungan ditentukan di dalam klausul ON JOIN, menggunakan fungsi DATEDIFF. Ukuran DATEDIFF maksimum adalah tujuh hari. Untuk informasi selengkapnya tentang penggunaan umumnya, lihat DATEDIFF (Azure Stream Analytics). Ketika DATEDIFF digunakan di dalam kondisi JOIN, parameter kedua dan ketiga mendapatkan perlakuan khusus.

Selain itu, SELECT * tidak dapat digunakan dalam pernyataan JOIN.

Sintaks

[ FROM { <input_source> } [ ,...n ] ]  
<input_source> ::=   
{  
    input_name [ [ AS ] input_alias ]   
    | <joined_table>   
}  
  
<joined_table> ::=   
{  
    <input_source> <join_type> <input_source> ON <join_condition>   
    | [ <input_source> <join_type> <reference_data> ON <join_condition> ]  
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ { INNER | LEFT [ OUTER ] } ] JOIN  
  

Argumen

<input_source>

Menentukan sumber data input.

<reference_data>

Data referensi tempat Anda ingin Bergabung dengan input_source Anda. Untuk informasi selengkapnya, lihat bagian Referensi Gabungan Data.

<join_type>

Menentukan jenis operasi gabungan.

IKUTI

Menunjukkan bahwa operasi gabungan yang ditentukan harus terjadi antara sumber input yang ditentukan dan /atau data referensi. Semua baris dari kiri dan kanan memenuhi kondisi gabungan disertakan dalam tataan hasil.

Peringatan

Jika sumber JOIN dipartisi, predikat JOIN harus menyertakan kondisi yang cocok dengan kunci partisi dari kedua sumber.

[ LEFT OUTER JOIN ]

Menentukan bahwa semua baris dari tabel kiri yang tidak memenuhi kondisi gabungan disertakan dalam kumpulan hasil, dan kolom output dari tabel lain diatur ke NULL selain semua baris yang dikembalikan oleh gabungan dalam.

ON <join_condition>

Menentukan kondisi di mana gabungan didasarkan. Kondisi gabungan harus memiliki batas waktu atau ruang geser temporal yang ditentukan untuk hubungan dan ditentukan di dalam klausul ON JOIN, menggunakan sintaks khusus Fungsi DATEDIFF Khusus untuk fungsi JOIN.

Contoh

Di Azure Stream Analytics, semua peristiwa memiliki tanda waktu yang ditentukan dengan baik. Dengan demikian, pengguna harus menggunakan alias baris langsung dalam fungsi DATEDIFF, sebagai berikut:

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  
  

Kondisi gabungan di atas akan menghasilkan kecocokan jika dan hanya jika ExitTime terjadi setelah EntryTime, tetapi tidak lebih dari 15 menit kemudian.

Catatan

DATEDIFF yang digunakan dalam pernyataan SELECT menggunakan sintaks umum di mana kolom atau ekspresi tanggalwaktu diteruskan sebagai parameter kedua dan ketiga. Namun, ketika fungsi DATEDIFF digunakan di dalam kondisi JOIN, nama input_source atau aliasnya digunakan. Secara internal tanda waktu yang terkait untuk setiap peristiwa di sumber tersebut dipilih.

Kondisi batas waktu dapat digabungkan satu sama lain dan dengan kondisi lain di dalam klausa ON, misalnya:

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  
  

Saat menggabungkan tiga tabel atau lebih, aturan yang sama berlaku --- batas waktu harus memastikan bahwa semua peristiwa yang cocok terjadi dalam jumlah waktu yang terbatas satu sama lain. Misalnya, untuk menemukan semua kesalahan yang terjadi antara peristiwa awal transaksi dan akhir transaksi, seseorang dapat mengatakan:

SELECT TS.Id, TS.Name, TS.Amount, E.ErrorCode, E.Description   
FROM TStart TS TIMESTAMP BY TStartTime   
JOIN TEnd TE TIMESTAMP BY TEndTime  
ON DATEDIFF(second, TS, TE) BETWEEEN 0 AND 5  
AND TS.Id = TE.Id  
JOIN Error E TIMESTAMP BY ErrorTime  
ON DATEDIFF(second, TS, E) BETWEEN 0 AND 5
AND DATEDIFF(second, TE, E) < 0
AND E.TId = TS.Id  
  

Saat menggabungkan sumber yang dipartisi, predikat JOIN harus menyertakan kondisi yang cocok dengan kunci partisi dari kedua sumber.

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime PARTITION BY PartitionId  
JOIN Input2 I2 TIMESTAMP BY ExitTime PARTITION BY PartitionId  
ON I1.PartitionId = I2.PartitionId AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  

Terakhir, Azure Stream Analytics mendukung gabungan dalam (default) dan gabungan luar LEFT. Untuk gabungan dalam, hasil hanya dikembalikan saat kecocokan ditemukan. Tetapi untuk gabungan LEFT OUTER, jika peristiwa dari sisi kiri gabungan tidak cocok, baris dengan NULL untuk semua kolom baris kanan dikembalikan. Misalnya, berikut adalah contoh untuk menemukan tidak adanya peristiwa. Kueri berikut akan mengembalikan baris-baris di mana Kendaraan telah memasuki Pintu Tol tetapi belum keluar dari Booth dalam waktu 15 menit.

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
LEFT OUTER JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF( minute , I1 , I2 ) BETWEEN 0 AND 15   
WHERE I2.TollId IS NULL  
  

Fungsi DATEDIFF Khusus untuk JOIN

Sintaks

DATEDIFF ( datepart , input_source1, input_source2 )  

Argumen

dateparts

Contoh. 'second', 'millisecond', 'minute', dll.)

input_source1

Sumber input pertama dalam Gabung. Secara internal tanda waktu yang terkait dengan peristiwa dari input_source ini diteruskan ke fungsi.

input_source2

Sumber input kedua dalam Gabungan. Secara internal tanda waktu yang terkait dengan peristiwa dari input_source ini diteruskan ke fungsi.

Jenis Hasil

Mengembalikan jumlah unit dalam datepart yang berlalu dari tanda waktu input_source1 ke tanda waktu input_source2. Nilai yang dikembalikan bisa negatif jika tanda waktu input_source kedua lebih besar dari yang pertama.