Tìm kiếm theo cụm từ khoá trong sql server 2016

[AutoReply]

Mình có bảng dữ liệu [TAIKHOAN] trong sql server giống như hình

trong đó cột [UserName] là chuỗi có phân cách nhau bởi dấu chấm (.)
Bây giờ mình có từ khoá cũng là 1 chuỗi có phân cách nhau bởi dấu chấm có dạng:

DECLARE @keyword VARCHAR(200) = '1542.apple' ;

Làm sao để truy vấn tìm ra các dòng có chứa tất cả các từ khoá con(sau khi tách ra bởi dấu chấm, ở đây là 2 từ khoá con: '1542' và 'apple') của @keyword ở trong bảng [TAIKHOAN] để ra được như hình

Nhờ các bạn chỉ giúp, cám ơn các bạn

 

[VetMini]

Phiên bản nào? Nếu SQL Server 2016 trở lên thì có thể dùng hàm STRING_SPLIT

DECLARE @keyword VARCHAR(200) = 'To.Ti.Te' ;
Select * IN Bang1 Where Cotx In (Select Value From STRING_SPLIT(@keyword, "."))
Đại khái bvaayj. Bạn biết dùng Cross Apply thì có thể làm ngược lại, hay gì gì đó.

Nếu phiên bản cũ hơn thì phải dùng voingf lặp tách nó ra rồi cho vào bảng tạm. Cuối cùng join.

Tôi đang tính thử xem CTE có thể làm gọn hơn mà chưa tìm ra giải thuật.

 

[AutoReply]

Phiên bản nào? Nếu SQL Server 2016 trở lên thì có thể dùng hàm STRING_SPLIT

DECLARE @keyword VARCHAR(200) = 'To.Ti.Te' ;
Select * IN Bang1 Where Cotx In (Select Value From STRING_SPLIT(@keyword, "."))
Đại khái bvaayj. Bạn biết dùng Cross Apply thì có thể làm ngược lại, hay gì gì đó.

Nếu phiên bản cũ hơn thì phải dùng voingf lặp tách nó ra rồi cho vào bảng tạm. Cuối cùng join.

Tôi đang tính thử xem CTE có thể làm gọn hơn mà chưa tìm ra giải thuật.

Thì đúng là mssql 2016 đó anh, nhưng mà truy vấn select IN chỉ nói được là có chứa từ khoá nằm trong @keyword, không bảo đảm được phải có chứa tất cả các cụm con của @keyword, vui ở chỗ đó anh ạ

 

[VetMini]

Cỡ rắc rối này thì phải viết UDF rồi.

 

[ongke0711]

Bạn thủ dùng ký tự đại diện xem thử: *1234*apple*

 

[VetMini]

Bạn thủ dùng ký tự đại diện xem thử: *1234*apple*

Tôi cũng nghĩ cái này rồi. Nhưng nó chỉ đúng nếu các cụm từ con sắp xếp đúng chỗ. Chỉ "chứa" theo bất cứ thứ tự nào thì bắt buộc phải search string.

Nếu join thì cái lệnh join rất dài cho nên tôi mới thử nghiên cứu CTE mà trước mắt thì còn bí.

Cái này dùng C# viết UDF thì ngon. Rất tiếc là cái máy chứa SQL Server và Visual Studio của tôi nó chết cứng hơn tháng rồi. Máy mới chưa có đủ tiền cài đặt.

 

[ongke0711]

Tôi cũng nghĩ cái này rồi. Nhưng nó chỉ đúng nếu các cụm từ con sắp xếp đúng chỗ. Chỉ "chứa" theo bất cứ thứ tự nào thì bắt buộc phải search string.

Nếu join thì cái lệnh join rất dài cho nên tôi mới thử nghiên cứu CTE mà trước mắt thì còn bí.

Cái này dùng C# viết UDF thì ngon. Rất tiếc là cái máy chứa SQL Server và Visual Studio của tôi nó chết cứng hơn tháng rồi. Máy mới chưa có đủ tiền cài đặt.

Vậy chủ thớt có xác định được cái chuỗi keyword có cố định được thứ tự không để khỏi phức tạp bài toán. Tôi nghĩ chắc cũng phải chuẩn hoá rồi.

 

[AutoReply]

Bạn thủ dùng ký tự đại diện xem thử: *1234*apple*

Chắc là không sử dụng được đâu anh. Danh sách cụm con trong @keyword nhiều hơn 2 cụm và cũng không xếp theo thứ tự

 

[AutoReply]

Tôi cũng nghĩ cái này rồi. Nhưng nó chỉ đúng nếu các cụm từ con sắp xếp đúng chỗ. Chỉ "chứa" theo bất cứ thứ tự nào thì bắt buộc phải search string.

Nếu join thì cái lệnh join rất dài cho nên tôi mới thử nghiên cứu CTE mà trước mắt thì còn bí.

Cái này dùng C# viết UDF thì ngon. Rất tiếc là cái máy chứa SQL Server và Visual Studio của tôi nó chết cứng hơn tháng rồi. Máy mới chưa có đủ tiền cài đặt.

Thư giản giải trí đầu óc thôi mà anh, đâu đến nỗi phải đem cả C# ra viết rùi nhúng vào sql thì hoành tráng quá.
Mà dạo này Ms khoe rằng Visual Code (không phải Visual Studio) là IDE "ngon" nhất thế giới + có thể soạn thảo mọi ngôn ngữ, chạy trên mọi hệ điều hành + miễn phí, có cái cho bác trải nghiệm. hihi

 

[ongke0711]

Thư giản giải trí đầu óc thôi mà anh, đâu đến nỗi phải đem cả C# ra viết rùi nhúng vào sql thì hoành tráng quá.
Mà dạo này Ms khoe rằng Visual Code (không phải Visual Studio) là IDE "ngon" nhất thế giới + có thể soạn thảo mọi ngôn ngữ, chạy trên mọi hệ điều hành + miễn phí, có cái cho bác trải nghiệm. hihi

Tôi thấy nhiều người giờ hay dùng VS code này lắm đó. Tôi cũng tập tành code python, office web add in bằng cái này

 

[ongke0711]

Chắc là không sử dụng được đâu anh. Danh sách cụm con trong @keyword nhiều hơn 2 cụm và cũng không xếp theo thứ tự

Bạn dùng code này thử xem:

DECLARE @keyword VARCHAR(200) = '1542.apple.'
DECLARE @FldName varchar(20)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)
DECLARE @OutputStr varchar (8000)

SET @FldName ='USERNAME'
SET @OutputStr=''
SET @pos = 0
SET @len = 0

WHILE CHARINDEX('.', @keyword, @pos+1)>0
BEGIN
    SET @len = CHARINDEX('.', @keyword, @pos+1) - @pos
    SET @value = SUBSTRING(@keyword, @pos, @len)
    SET @OutputStr = @OutputStr + @FldName + ' Like ''*' + @value + '*'' AND '
    SET @pos = CHARINDEX('.', @keyword, @pos+@len) +1
END
SET @OutputStr=LEFT(@OutputStr,LEN(@outputstr)-4)
PRINT @OutputStr --for check only

USERNAME Like '*1542*' AND USERNAME Like '*apple*'

 

[AutoReply]

Có 1 thuật toán sẽ phát huy hết "sức mạnh" của nó khi áp dụng vào bài toán này.
Thuật toán tạm gọi tên là: Khẳng định = phủ định 2 lần
Yêu cầu bài toán là tìm các dòng có UserName chứa tất cả các cụm con nằm trong @keyword.
Ta sẽ chuyển cách giải thành: tìm các dòng mà Không tồn tại cụm con nào của @keyword không chứa trong UserName.
Như vậy ta được truy vấn:

     DECLARE @keyword VARCHAR(200) = '1542.apple'
    
    SELECT * FROM TAIKHOAN WHERE
    -- LEN(@keyword) > 0 and
    NOT exists (SELECT 0 from string_split(@keyword,'.')
    WHERE CHARINDEX(CONCAT('.',value,'.'), CONCAT('.',UserName,'.')) = 0
    )

Nếu các bạn có những cách làm thú vị, xin hãy cùng trao đổi. Chúc các bạn có những giây phút thư giãn vui vẻ

 

[ongke0711]

Vậy thì cũng nên xem execution plan nó như thế nào.

 

[hoanganhvo2612]

Bài này mình giải ntn:
declare @tong int
select @tong = count(*) from STRING_SPLIT(chuoi,'.')

Select username
from table a
join STRING_SPLIT(chuoi,'.') b on CHARINDEX(CONCAT('.',value,'.'), CONCAT('.',UserName,'.')) <> 0
group by username
having count(*) = tong

 

[VetMini]

Dùng cách đếm số join được như bài #14 có lẽ là tối ưu.
Hầu hết các CSDL LH đều được bên phát triển phần mềm và thiết kế đặt nặng ở phép join và phép group. Cứ thuật toán nào sử dụng hai thứ này hiệu quả.

 

[AutoReply]

Dùng cách đếm số join được như bài #14 có lẽ là tối ưu.
Hầu hết các CSDL LH đều được bên phát triển phần mềm và thiết kế đặt nặng ở phép join và phép group. Cứ thuật toán nào sử dụng hai thứ này hiệu quả.

Mình rất hoan nghênh và cám ơn các bạn đã tham gia góp ý các giải pháp hữu ích, nhưng đánh giá phải dựa trên sự khách quan. Mình rất cảm ơn các bạn đánh giá, nhưng bài #14 mới đi được nửa đường, vì bài #14 mới trả về bảng 1 cột, muốn tới được bài #12 phải thêm 1 query nữa để nối 1 cột đó thành nguyên bảng, lúc đó chưa biết ai về trước ai nha anh. Chưa nói tới việc phép join chỉ thực sự hiệu quả khi bảng join dựa vào các index đã lập trước, trong khi join của bài này phép join dựa vào biểu thức CONCAT, tức là đã phá bỏ mỏi lợi thế của phép JOIN của nhà sản xuất (dựa vào index của bảng). Nhưng mà cũng rất cảm ơn các anh em đã giúp đỡ nhiệt tình để mình có cái nhìn toàn diện.

 

[VetMini]

... Chưa nói tới việc phép join chỉ thực sự hiệu quả khi bảng join dựa vào các index đã lập trước, trong khi join của bài này phép join dựa vào biểu thức CONCAT, tức là đã phá bỏ mỏi lợi thế của phép JOIN của nhà sản xuất (dựa vào index của bảng). Nhưng mà cũng rất cảm ơn các anh em đã giúp đỡ nhiệt tình để mình có cái nhìn toàn diện.

Cái algorithm của join nó khá phức tạp. Lúc không có index thì nó sẽ dùng kiểu spread và sort.
Chỉ là ở đây, vì match không trực tiếp (có wildcards) cho nên một số thủ thuật như dò nhị phân, hay dò b-tree mất hiệu quả.

 

[AutoReply]

Cái algorithm của join nó khá phức tạp. Lúc không có index thì nó sẽ dùng kiểu spread và sort.
Chỉ là ở đây, vì match không trực tiếp (có wildcards) cho nên một số thủ thuật như dò nhị phân, hay dò b-tree mất hiệu quả.

Vậy chính anh cũng thấy rằng bài toán này này phép Join không còn hiệu quả, vì không thể dựa trên so sánh bằng, không thể dùng index gì cho phép join vì nếu bỏ Concat thì kết quả đương nhiên ra sai, vậy điều gì khiến anh nghĩ truy vấn bài #14 tối ưu hơn bài #12 khi nó mới chỉ đi được nửa đường tới kết quả ?