SQL टिप्स और ट्रिक्स

 (github.com/ben-n93)
7 पॉइंट द्वारा GN⁺ 2024-09-26 | 2 टिप्पणियां | WhatsApp पर शेयर करें

विषय सूची

फ़ॉर्मैटिंग/पठनीयता

  • फ़ील्ड अलग करते समय leading comma का उपयोग
  • WHERE clause में dummy value का उपयोग
  • उचित code indentation
  • जटिल query लिखते समय CTE पर विचार करें

उपयोगी फीचर्स

  • :: operator का उपयोग करके data type conversion
  • anti join का उपयोग
  • window function filtering के लिए QUALIFY का उपयोग
  • column position से GROUP BY का उपयोग किया जा सकता है

जिन गलतियों से बचना चाहिए

  • NULL values के साथ NOT IN का उपयोग करते समय सावधान रहें
  • ambiguity से बचने के लिए calculated field का नाम बदलें
  • यह स्पष्ट करें कि हर column किस table से आता है
  • execution order को समझें
  • code में comments लिखें
  • पूरा documentation पढ़ें

फ़ॉर्मैटिंग/पठनीयता

फ़ील्ड अलग करते समय leading comma का उपयोग

  • SELECT clause में फ़ील्ड अलग करते समय leading comma का उपयोग करने से नई columns को स्पष्ट रूप से अलग किया जा सकता है
  • leading comma इस बात का visual clue देता है कि कहीं comma छूटा तो नहीं है
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

WHERE clause में dummy value का उपयोग

  • WHERE clause में dummy value का उपयोग करके conditions को dynamically जोड़ना और हटाना आसान हो सकता है
SELECT *
FROM employees
WHERE 1=1 -- dummy value
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

उचित code indentation

  • code को indent करने से readability बढ़ती है और teammates तथा भविष्य में खुद के लिए code समझना आसान होता है
-- खराब उदाहरण:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- अच्छा उदाहरण:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

जटिल query लिखते समय CTE पर विचार करें

  • nested inline views की जगह common table expression (CTE) का उपयोग करने से code की readability और structure बेहतर हो सकती है
-- inline view का उपयोग:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- CTE का उपयोग:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

उपयोगी फीचर्स

:: operator का उपयोग करके data type conversion

  • कुछ RDBMS में :: operator का उपयोग करके value को किसी दूसरे data type में convert किया जा सकता है
SELECT CAST('5' AS INTEGER); -- CAST function का उपयोग
SELECT '5'::INTEGER; -- :: syntax का उपयोग

anti join का उपयोग

  • anti join उन rows को लौटाने में बहुत उपयोगी है जो सिर्फ एक table में मौजूद हों
  • subquery का उपयोग भी किया जा सकता है, लेकिन आम तौर पर anti join अधिक तेज़ होता है
-- anti join:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- subquery:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- correlated subquery:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

window function filtering के लिए QUALIFY का उपयोग

  • QUALIFY का उपयोग करके window function के result को filter किया जा सकता है
  • यह code lines कम करने में उपयोगी है
-- QUALIFY का उपयोग:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- QUALIFY के बिना:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

column position से GROUP BY का उपयोग किया जा सकता है

  • column name की जगह column position का उपयोग करके GROUP BY या ORDER BY किया जा सकता है
  • ad-hoc queries के लिए यह उपयोगी है, लेकिन production code में हमेशा column names को refer करना चाहिए
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no `SELECT` clause का पहला column है
ORDER BY 2 DESC;

जिन गलतियों से बचना चाहिए

NULL values के साथ NOT IN का उपयोग करते समय सावधान रहें

  • NOT IN तब काम नहीं करता जब उसमें NULL values मौजूद हों
  • इसकी जगह NOT EXISTS का उपयोग करना चाहिए
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- NULL value की वजह से काम नहीं करता
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- समाधान
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

ambiguity से बचने के लिए calculated field का नाम बदलें

  • calculated field को किसी existing column name से rename करने पर unexpected behavior हो सकता है
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- window function 'Robot' product को rank 1 देता है
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

यह स्पष्ट करें कि हर column किस table से आता है

  • complex queries में हर column किस table से आता है, यह स्पष्ट करने से issues को trace करना आसान होता है
SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

execution order को समझें

  • SQL सीखने वालों के लिए सबसे महत्वपूर्ण सलाह execution order को समझना है
  • execution order समझने से query लिखने का तरीका पूरी तरह बदल जाता है

code में comments लिखें

  • code लिखते समय ऐसे comments जोड़ने चाहिए जो कारण समझाएँ
  • teammates और भविष्य में आप खुद इसके लिए आभारी होंगे
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- नया CMS archived video formats को संभाल नहीं सकता
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

पूरा documentation पढ़ें

  • documentation को पूरा पढ़ने से अप्रत्याशित समस्याओं से बचा जा सकता है
  • इसे पढ़ने में सिर्फ कुछ मिनट लगते हैं और यह unexpected issues हल करने में मदद कर सकता है
-- अगर documentation और पढ़ी होती, तो NULL समस्या हल हो सकती थी
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- GREATEST_IGNORE_NULLS function भी उपलब्ध है
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

GN⁺ का सारांश

  • यह लेख SQL को अधिक कुशल और पठनीय तरीके से लिखने के लिए कई टिप्स और ट्रिक्स देता है
  • SQL पहली बार सीखने वालों से लेकर अनुभवी data analysts तक, सभी के लिए इसमें उपयोगी जानकारी है
  • खासकर जटिल queries लिखते समय CTE का उपयोग, anti join का उपयोग, और QUALIFY का उपयोग जैसे बिंदु practical काम में बहुत मददगार होंगे
  • SQL का execution order समझना, code में comments लिखना, और documentation को ध्यान से पढ़ने की आदत डालना महत्वपूर्ण है
  • समान फीचर्स वाले अन्य tools में PostgreSQL, MySQL, Oracle आदि शामिल हैं

संबंधित पढ़ाई

2 टिप्पणियां

 
hiyama 2024-09-26

इस पोस्ट में सभी leading commas को trailing commas के रूप में लिखा गया है। मूल पाठ में वे leading के रूप में दर्ज हैं.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt;= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
Hacker News राय

  • DB सर्वर को अच्छी तरह समझने और query plan को अक्सर जांचने की जरूरत

    • कई मामलों में EXISTS, IN से तेज़ होता है
    • NOT EXISTS और EXCEPT, NULL values को अलग तरीके से handle करते हैं
    • table join की जगह subquery columns इस्तेमाल करने की सिफारिश
    • table scan से बचना चाहिए और index जोड़ना चाहिए
    • expression filtering के समय computed columns और index का इस्तेमाल किया जा सकता है
    • UNION ALL, OR से तेज़ हो सकता है
    • subquery JOIN के जरिए filtering order को force किया जा सकता है

  • जटिल stored procedure काम के लिए टिप्स

    • permanent table को temporary table में copy करें और सिर्फ जरूरी rows को filter करें
    • temporary table को manipulate करें
    • transaction के अंदर permanent table update करें, error होने पर rollback करें
    • remote table पर काम करते समय सावधानी जरूरी, temporary table में copy करके काम करने की सिफारिश
    • query plan उलझाऊ हो सकता है, इसलिए काम को छोटे steps में बांटें
    • query plan हमेशा जांचें

  • code readability पर राय

    • पहले दो उदाहरण readability की कीमत पर लिखने में आसानी बढ़ाते हैं
    • आखिरी उदाहरण में indentation का खास असर नहीं है

  • SQL में FROM-first और piping syntax इस्तेमाल करने का सुझाव

    • Kusto query language इस्तेमाल करने का अनुभव बड़ा सुधार साबित हुआ

  • Anti Join से जुड़ी टिप

    • EXISTS इस्तेमाल करने की सिफारिश, condition-based subquery में row के मौजूद होने की जांच के लिए फायदेमंद

  • SELECT statement में leading comma इस्तेमाल करने के फायदे

    • individual lines को comment out किया जा सकता है
    • code indentation से readability बेहतर होती है

  • MSSQL में comments इस्तेमाल करते समय -- की जगह /* */ इस्तेमाल करने की सिफारिश

    • क्योंकि query store queries को line breaks के बिना store करता है

  • window functions इस्तेमाल करने की सिफारिश

  • WHERE clause में 1=1 इस्तेमाल करने पर बहस

  • AI2sql परिचय

    • plain English prompt से SQL query generate की जा सकती है
    • complex queries लिखते समय उपयोगी है