Bảng cheat SQL cho cuộc phỏng vấn khoa học dữ liệu
Bảng cheat SQL này cung cấp cho bạn một cái nhìn tổng quan ngắn gọn và thiết thực về các khái niệm SQL mà bạn sẽ cần cho cuộc phỏng vấn xin việc Show
Chúng tôi dự định dành cho bảng cheat SQL này để giúp bạn bất cứ khi nào bạn cần lướt qua một số khái niệm SQL và ghi nhớ cách chúng hoạt động. Có một cheat sheet để tham khảo đặc biệt hữu ích nếu bạn vẫn chưa quen với một số khái niệm hoặc không sử dụng chúng thường xuyên Trong bảng cheat SQL này, chúng tôi sẽ giải thích ngắn gọn. Cùng với chúng, mọi khái niệm sẽ được giải thích bằng biểu diễn đồ họa và câu hỏi phỏng vấn từ nền tảng StrataScratch Và các khái niệm chúng ta sẽ trình bày trong bảng cheat SQL này là 1. THAM GIA
2. Chức năng thời gian và ngày tháng
3. Chức năng tổng hợp
4. Chức năng cửa sổ
5. Đặt toán tử
Bảng cheat SQL. THAM GIAMệnh đề THAM GIA trong SQL cho phép bạn sử dụng dữ liệu từ hai hoặc nhiều bảng bằng cách nối chúng qua cùng một cột được tìm thấy trong cả hai bảng THAM GIA thường xuất hiện nhiều nhất trong cuộc phỏng vấn việc làm là
CHỖ NỐI BÊN TRÁIGiải trìnhLEFT JOIN hoặc LEFT OUTER JOIN trả về tất cả dữ liệu từ bảng đầu tiên hoặc bảng bên trái và chỉ các hàng phù hợp từ bảng thứ hai hoặc bảng bên phải. Nếu có các hàng không khớp, nó sẽ trả về giá trị NULL Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10291-sms-confirmations-from-users Dung dịch
LEFT JOIN kết nối bảng fb_sms_sends (bảng bên trái) với bảng fb_confirmers. Nó thực hiện điều đó trên các cột ds và ngày từ cả hai bảng. Sử dụng loại THAM GIA này sẽ giúp bạn có được tất cả các hàng từ bảng fb_sms_sends và chỉ các hàng phù hợp từ bảng thứ hai THAM GIA BÊN TRONGGiải trìnhJOIN hoặc INNER JOIN chỉ xuất ra các hàng phù hợp hoặc các hàng được tìm thấy trong cả hai bảng Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10187-tìm-the-tổng-số-giường-có-sẵn-cho-mỗi-máy-chủ-quốc Dung dịch
INNER JOIN sẽ nối bảng airbnb_hosts với bảng airbnb_apartments. Cột thực hiện việc này là host_id. Tham gia này sẽ chỉ trả về các máy chủ xuất hiện trong cả hai bảng nhiều tham giaGiải trìnhNhiều phép nối không phải là một kiểu THAM GIA đặc biệt. Nó đơn giản có nghĩa là tham gia nhiều hơn hai bảng trong khi sử dụng bất kỳ loại SQL nào THAM GIA. Nó hoạt động giống như cách nối hai bảng, nhưng bạn thêm ba, bốn, năm hoặc bất kỳ số lượng bảng nào vào chuỗi THAM GIA Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10300-premium-vs-freemium Dung dịch
Trong giải pháp này, ba bảng được nối trong truy vấn con bằng INNER JOIN. Đầu tiên, các bảng ms_user_dimension và ms_acc_dimension được nối trên cột acc_id. Sau đó, bảng thứ ba, ms_download_facts, được thêm vào bằng INNER JOIN thứ hai. Bảng này được nối với bảng đầu tiên trên cột user_id TỰ THAM GIAGiải trìnhMột lần nữa, tự tham gia không phải là một mệnh đề THAM GIA cụ thể trong SQL. Bất kỳ THAM GIA nào cũng có thể được sử dụng để tự tham gia. Nó có nghĩa là bạn đang tham gia vào bảng với chính nó Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10011-tìm-tất-cả-số-cặp-có-số-thứ-đầu-nhỏ-hơn-thứ-thứ-hai-một-và-tích-của-hai-số-là-lớn-hơn- Dung dịch
Giải pháp này sử dụng INNER JOIN để tham gia bảng shipping_numbers với chính nó. Nó được thực hiện bằng cách để bảng xuất hiện hai lần trong INNER JOIN. một lần là bàn bên trái và lần thứ hai là bàn bên phải. Cả hai bảng đều được đặt bí danh để bạn có thể phân biệt giữa chúng. Bảng được tự tham gia vào chỉ mục cột Bảng cheat SQL. Chức năng thời gian và ngày thángCác hàm thời gian và ngày tháng là các hàm SQL được sử dụng để tính toán và thao tác các kiểu dữ liệu ngày và giờ. Chúng có thể khác nhau giữa các phương ngữ SQL, nhưng ba phương ngữ phổ biến nhất trong PostgreSQL là
DATE_PART()Giải trìnhHàm này được sử dụng để lấy một phần ngày hoặc giờ từ dữ liệu. Ví dụ: nó có thể trả về một năm hoặc tháng kể từ ngày hoặc phút từ dữ liệu thời gian Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/9762-tìm-ngày-trong-tuần-có-nhiều-người-đăng-ở nhất Dung dịch
Hàm DATE_PART được áp dụng cho cột ds_checkin từ bảng airbnb_contacts. Để có được ngày trong tuần, 'dow' phải được viết trong hàm TRÍCH XUẤT()Giải trìnhHàm EXTRACT() thực hiện tương tự như hàm DATE_PART(). nó trả về một phần của ngày hoặc giờ. Đây là một hàm tuân theo tiêu chuẩn SQL, trong khi DATE_PART() là hàm PostgreSQL Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/2039-products-report-summary Dung dịch
Hàm EXTRACT() được sử dụng trên cột ngày giao dịch để trích xuất năm từ cột đó. Được sử dụng trong mệnh đề WHERE như trên có nghĩa là nó sẽ chỉ hiển thị các giao dịch từ năm 2017 THỜI GIANGiải trìnhHàm INTERVAL được sử dụng để cộng hoặc trừ một khoảng thời gian cho ngày hoặc giờ đã tồn tại Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/2004-số-bình-luận-mỗi-người-dùng-trong-30-ngày-qua Dung dịch
Để trả lời câu hỏi, cần tìm ngày 30 ngày trước ngày hiện tại. Giả định là ngày hiện tại là 2020-02-10. Trừ INTERVAL '1 ngày' từ nó có nghĩa là nó sẽ trừ đi một ngày từ 2020-02-10, tức là 2020-02-09. Nếu 30 * INTERVAL '1 ngày' bị trừ đi, điều này có nghĩa là 30 ngày bị trừ khỏi ngày hiện tại. Điều này dẫn đến việc nhận được các nhận xét từ 30 ngày qua. Để đảm bảo không có bình luận nào sau ngày hôm nay, có điều kiện thứ hai trong mệnh đề WHERE Bảng cheat SQL. Chức năng tổng hợpCác hàm tổng hợp SQL là các hàm thực hiện phép tính trên tập hợp các hàng và trả về một giá trị duy nhất Chúng có thể được biểu diễn theo cách sau Các chức năng thường được sử dụng nhất là
Các chức năng tổng hợp cũng có thể được sử dụng như chức năng cửa sổ. Chúng là gì sẽ được giải thích trong phần chức năng của cửa sổ ĐẾM()Giải trìnhHàm COUNT() đếm hàng. Có ba cách sử dụng chức năng này. COUNT(*) trả về tất cả các hàng từ bảng, bao gồm các giá trị trùng lặp và giá trị NULL Cách sử dụng thứ hai là COUNT(biểu thức). Cái này sẽ trả về tất cả các hàng từ bảng, bao gồm cả các bản sao, nhưng sẽ bỏ qua các giá trị NULL Kết quả COUNT (biểu thức DISTINCT) là số hàng riêng biệt (duy nhất) không có giá trị NULL Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10178-business-open-on-sunday Dung dịch
Sau khi các bảng được nối và lọc dữ liệu, sử dụng hàm COUNT(*) sẽ đếm số lượng doanh nghiệp trên mỗi khoảng thời gian hoạt động. Trong trường hợp này, sử dụng COUNT(business. business_id) hoặc COUNT(DISTINCT doanh nghiệp. business_id) sẽ mang lại cho bạn kết quả tương tự TỔNG()Giải trìnhHàm SUM() là một hàm tổng hợp được sử dụng để lấy tổng giá trị của các hàng Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10127-calculate-samanthas-and-lisas-total-sales-revenue Dung dịch ________số 8Giải pháp sử dụng hàm SUM() trên cột sales_revenue để tính doanh số của Samantha và Lisa AVG()Giải trìnhHàm AVG() tính giá trị trung bình của các hàng. Nói cách khác, nó tính tổng các giá trị của các hàng và chia kết quả cho số lượng hàng. Nếu có giá trị NULL, AVG() sẽ bỏ qua nó, tôi. e. nó chỉ chia tổng cho số hàng có giá trị không NULL Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi https. //nền tảng. tầng lớp. com/coding/10077-thu-nhien-theo-chức-vụ-giới-tính Dung dịch
Hàm AVG() được áp dụng trên tổng của cột lương và ttl_bonus, vì hai cột này cấu thành tổng lương thưởng. Do đó, truy vấn sẽ hiển thị mức lương trung bình theo chức danh và giới tính của nhân viên NHỎ NHẤT LỚN NHẤT()Giải trìnhCác hàm tổng hợp MIN() và MAX() là hai mặt của cùng một đồng xu. MIN() trả về giá trị nhỏ nhất hoặc thấp nhất, trong khi MAX() sẽ hiển thị giá trị lớn nhất hoặc cao nhất Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10310-class-hiệu suất Dung dịch 0Để có được sự khác biệt lớn nhất trong tổng số điểm, điểm tối đa và tối thiểu phải được tính toán. Hàm MAX() và MIN() sẽ làm việc đó khi áp dụng cho cột điểm. Khi các giá trị này được tính toán, chúng phải được trừ đi để có được sự khác biệt giữa điểm cao nhất và điểm thấp nhất Bảng cheat SQL. Chức năng cửa sổCác hàm cửa sổ SQL là các hàm thực hiện phép tính trên các hàng có liên quan đến hàng hiện tại Chúng tương tự như các hàm tổng hợp được sử dụng với mệnh đề GROUP BY. Sự khác biệt chính là các hàm cửa sổ không thu gọn các hàng riêng lẻ trong khi hiển thị các giá trị tổng hợp Đây là cách họ làm việc Chúng có thể được chia thành ba loại
Tên chức năng của cửa sổ xuất phát từ khung cửa sổ. Đó là cách chia tập hợp dữ liệu thành các khối. Khung cửa sổ có thể là tất cả các hàng có cùng dữ liệu trong (các) cột được chỉ định. Hoặc nó có thể là một số hàng nhất định trước và sau hàng hiện tại Cú pháp chức năng cửa sổ là window_function ( biểu thức ) QUÁ ([PHẦN PHẦN THEO list_of_columns] [ĐẶT HÀNG THEO list_of_columns] [ROW hoặc mệnh đề RANGE]) PHÂN BẰNG Mệnh đề PARTITION BY được sử dụng để xác định khung cửa sổ. Nói cách khác, cột mà theo đó dữ liệu sẽ được phân vùng được xác định bằng cách sử dụng lệnh PARTITION BY. Chức năng cửa sổ sẽ được thực thi trên từng phân vùng riêng biệt khi điều này được thực hiện ĐẶT BỞI ORDER BY là một mệnh đề con khác được sử dụng trong mệnh đề OVER(). Không giống như ORDER BY 'thông thường', nó không được sử dụng để sắp xếp dữ liệu trong đầu ra truy vấn. Trong các chức năng cửa sổ, nó được sử dụng để chỉ định thứ tự thực hiện chức năng cửa sổ. Điều này có thể theo thứ tự tăng dần hoặc giảm dần PHẠM VI hoặc HÀNG Phần quan trọng thứ ba của mệnh đề OVER() là mệnh đề RANGE hoặc ROW. Chúng cũng được sử dụng để xác định khung cửa sổ. Họ không làm theo cột của bảng như PARTITION BY. Các mệnh đề RANGE và ROW xác định các hàng bắt đầu và kết thúc của khung cửa sổ Mệnh đề RANGE thực hiện điều đó một cách logic bằng cách chỉ định các hàng liên quan đến giá trị của hàng hiện tại
Các tùy chọn để sử dụng các mệnh đề này là
Các tùy chọn này có thể được kết hợp tự do theo nhu cầu CẤP()Giải trìnhRANK() là hàm xếp hạng dữ liệu trong một bảng và bỏ qua các ràng buộc. Điều này có nghĩa là nó sẽ phân bổ cùng một thứ hạng trong đó các giá trị giống nhau, nhưng giá trị không liên kết tiếp theo sẽ được xếp hạng không theo thứ tự. Nó sẽ bỏ qua số bậc phù hợp với số mối quan hệ Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/2022-update-call-duration Dung dịch 1Giải pháp trên sử dụng hàm cửa sổ RANK() trong truy vấn con. Sau hàm RANK(), mệnh đề OVER() được viết, báo hiệu hàm cửa sổ Dữ liệu được phân vùng theo ID yêu cầu, có nghĩa là mọi ID yêu cầu sẽ được xếp hạng riêng. ORDER BY nghĩa là thứ tự thực hiện xếp hạng. Trong trường hợp này, xếp hạng sẽ được thực hiện theo ngày tạo theo thứ tự tăng dần Trong thực tế, điều này có nghĩa là hàm RANK() sẽ xếp hạng ID yêu cầu từ ngày cũ nhất đến ngày mới nhất. Khi tất cả các ngày trong một ID yêu cầu được xếp hạng, các chức năng của cửa sổ sẽ chuyển đến ID yêu cầu tiếp theo và bắt đầu xếp hạng lại từ 1 DENSE_RANK()Giải trìnhDENSE_RANK() cũng là Chức năng Cửa sổ Xếp hạng. Không giống như RANK(), nó không bỏ qua các ràng buộc. Điều này có nghĩa là tất cả các hàng không được liên kết sẽ được xếp hạng theo thứ tự, trong khi các giá trị được liên kết sẽ có cùng thứ hạng Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10161-rating-hosts-by-bed Dung dịch 2Hàm DENSE_RANK() được giới thiệu ở đây, cũng sử dụng mệnh đề OVER(). Không có mệnh đề PARTITION BY, có nghĩa là việc xếp hạng sẽ được thực hiện trên toàn bộ bảng. Dữ liệu sẽ được xếp hạng theo tổng số giường từ cao nhất đến thấp nhất SỐ LƯỢNG HÀNGGiải trìnhChức năng xếp hạng thứ ba phân bổ số hàng cho các hàng. Số hàng được phân bổ tuần tự, cũng bao gồm các giá trị được liên kết. Cũng có thể ROW_NUMBER không trả về cùng một số hàng mỗi lần. Điều này xảy ra khi các giá trị hoặc sự kết hợp của các giá trị trong các cột được sử dụng trong PARTITION BY và ORDER BY không phải là duy nhất Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10351-activity-rank Dung dịch 3Do câu hỏi yêu cầu trả về một giá trị xếp hạng duy nhất ngay cả khi người dùng có cùng số lượng email, ROW_NUMBER() được sử dụng để xếp hạng. Tên hàm được theo sau bởi mệnh đề OVER() như trong các ví dụ trước. Xếp hạng ở đây được thực hiện theo số lượng email theo thứ tự giảm dần và theo thứ tự bảng chữ cái nếu có mối quan hệ Bảng cheat SQL. Đặt toán tửCác toán tử này cho phép kết hợp đầu ra của nhiều truy vấn vào một tập dữ liệu duy nhất Hai toán tử được sử dụng nhiều nhất là
LIÊN HIỆPGiải trìnhToán tử UNION chỉ trả về các giá trị duy nhất từ đầu ra của hai hoặc nhiều truy vấn. Phải có số lượng cột bằng nhau trong tất cả các truy vấn để toán tử này hoạt động. Các cột cũng phải có cùng kiểu dữ liệu giữa chúng Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10025-find-all-possible-varieties-what-occur-in-either-of-the-winemag-datasets Dung dịch 4Các loại rượu riêng biệt phải được tìm thấy riêng biệt bằng cách sử dụng hai câu lệnh CHỌN. Cái đầu tiên sẽ truy vấn bảng winemag_p1, cái thứ hai sẽ truy vấn bảng winemag_p2. Vì câu hỏi chỉ yêu cầu xuất ra các giống duy nhất, toán tử UNION cần được sử dụng. nó được viết đơn giản giữa hai câu lệnh SELECT CÔNG ĐOÀN TẤT CẢGiải trìnhToán tử UNION ALL thực hiện giống như toán tử UNION, với một điểm khác biệt quan trọng. UNION ALL trả về tất cả dữ liệu từ tất cả các truy vấn, bao gồm cả các giá trị trùng lặp Đại diện đồ họacâu hỏi phỏng vấnLiên kết đến câu hỏi. https. //nền tảng. tầng lớp. com/coding/10008-sum-of-numbers Dung dịch 5Truy vấn đầu tiên sẽ trả về tổng các số có chỉ số nhỏ hơn 5. Truy vấn thứ hai sẽ thực hiện tương tự, nhưng đối với chỉ mục trên 5. Để hợp nhất kết quả đầu ra của cả hai truy vấn, UNION ALL được viết giữa các câu lệnh SELECT. Tại sao UNION ALL thay vì UNION? . Trên thực tế, chúng giống nhau. Nếu UNION được sử dụng, kết quả sẽ chỉ được hiển thị trong một hàng. Điều này có nghĩa là không nhận được kết quả chính xác vì câu hỏi yêu cầu xuất kết quả trong các hàng riêng biệt |