MySQL WITH වාක්‍ය (CTE) පැහැදිලි කිරීම: ව්‍යවස්ථාව, උදාහරණ, පූනරාවර්තන ප්‍රශ්න සහ කාර්ය සාධන උපදෙස්

1. Introduction

MySQL යනු සංවර්ධකයින් සහ දත්ත ගබඩා පරිපාලකයින් විසින් පුළුල් ලෙස භාවිතා කරන දත්ත ගබඩා කළමනාකරණ පද්ධතියක් වන අතර, ශක්තිමත් හා සවිස්තරාත්මක SQL කාර්ය සාධනය ලබා දේ. එහි විශේෂාංග අතර, MySQL 8.0 හි හඳුන්වා දී ඇති WITH clause (Common Table Expression: CTE) යනු SQL විමසුම් වඩාත් කියවීමට පහසු කරමින්, නඩත්තුකිරීමේ හැකියාව වැඩි කරන ශක්තිමත් මෙවලමකි.

මෙම ලිපිය WITH clause මූලික කරුණු සිට උසස් භාවිතය දක්වා, ආරම්භකයන් සිට මධ්‍යම පරිශීලකයන් දක්වා අරමුණු කරගෙන පැහැදිලි කරයි. විශේෂයෙන්, උපවිමසුම් (subqueries) ප්‍රතිස්ථාපනය කිරීම සහ ප්‍රතිචක්‍ර (recursive) විමසුම් ක්‍රියාත්මක කිරීම වැනි ප්‍රායෝගික විෂයයන් ආවරණය කරයි.

SQL ඉගෙන ගන්නා අය හෝ දිනපතා වැඩේදී විමසුම් සුදානම් කිරීමේ ගැටළු වලට මුහුණ දෙන අය සඳහා, මෙම ලිපිය ප්‍රායෝගික විසඳුම් ලබා දීමට උත්සාහ කරයි. පහත අන්තර්ගතය අනුගමනය කර WITH clause හි මූලික කරුණු තේරුම් ගනිමින්, ඒවා වාස්තුකලාවලදී යෙදවීමට හැකියාව ලබා ගන්න.

2. Basics of the WITH Clause (Common Table Expressions)

What Is the WITH Clause?

WITH clause යනු SQL විමසුමක් තුළ තාවකාලික ප්‍රතිඵල කට්ටලයක් (Common Table Expression, CTE) නිර්වචනය කර, පසු විමසුම් වල එය යොමු කිරීම සඳහා භාවිතා කරන ව්‍යවස්ථාමය රීතියකි. MySQL 8.0 සිට සහය දක්වන මෙම රීතිය, සංකීර්ණ උපවිමසුම් පැහැදිලි හා කෙටි ආකාරයකට නැවත ලියීමට ඉඩ සලසයි.

උදාහරණයක් ලෙස, උපවිමසුම් සෘජුව ලියන විට කියවීමට අපහසු විය හැකි අතර, විමසුම දිගුවත් විය හැක. WITH clause භාවිතා කිරීමෙන් විමසුම් තාර්කික කොටස් ලෙස බෙදා, ඒවා තේරුම් ගැනීම පහසු කරයි.

Basic Syntax of the WITH Clause

පහත දැක්වෙන්නේ WITH clause හි මූලික ව්‍යවස්ථාවයි:

WITH table_name AS (
  SELECT column1, column2
  FROM original_table
  WHERE condition
)
SELECT column1, column2
FROM table_name;

මෙම ව්‍යවස්ථාවේ, WITH පසු වර්චුවල් වගුව (Common Table Expression) එකක් නිර්වචනය කර, එය ප්‍රධාන විමසුමේ භාවිතා කරයි. මෙය නැවත නැවත භාවිතා වන උපවිමසුම් කෙටි ලෙස ප්‍රකාශ කිරීමට පහසු කරයි.

Differences from Subqueries and Views

WITH clause තාවකාලික ප්‍රතිඵල කට්ටලයක් සෑදීමෙන්, උපවිමසුම් සහ දෘශ්‍ය (views) සමඟ කිහිපයක් වෙනස් කරයි.

WITH clause උපවිමසුම් වලට වඩා කියවීමට පහසු වන අතර, දෘශ්‍යයක් වැනි ස්ථිර වස්තුවක් නිර්මාණය කිරීමට අවශ්‍ය නොවූ විට ඉතා සුදුසු වේ.

Benefits of Using the WITH Clause

1. විමසුම් කියවීමට පහසුකම වැඩි වීම බහු උපවිමසුම් තිබුණත්, WITH clause සමඟ ඒවා සංවිධානය කිරීමෙන් ව්‍යුහය පැහැදිලි වේ.
2. ප්‍රතිභාවිතා හැකියාව වැඩි වීම තාවකාලික ප්‍රතිඵල කට්ටලයක් නිර්වචනය කිරීමෙන්, එය විමසුමේ බහු වරක් යොමු කළ හැක.
3. නඩත්තුකිරීමේ හැකියාව වැඩි වීම විමසුම් තාර්කික කොටස් ලෙස බෙදා ගැනීමෙන්, වෙනස්කම් සහ විස්තාරණය කිරීම පහසු වේ.

3. Basic Usage of the MySQL WITH Clause

Replacing Subqueries

WITH clause සංකීර්ණ උපවිමසුම් සරල කිරීම සඳහා ශක්තිමත් මෙවලමකි. උපවිමසුම් සෘජුව ඇතුළත් කිරීමෙන් සම්පූර්ණ විමසුම සංකීර්ණ හා කියවීමට අපහසු වේ, නමුත් WITH clause භාවිතා කිරීමෙන් කියවීමට පහසුකම වැඩි වේ.

පහත දැක්වෙන්නේ උපවිමසුමක් WITH clause භාවිතයෙන් ප්‍රතිස්ථාපනය කිරීමේ මූලික උදාහරණයකි.

උපවිමසුමක් භාවිතා කිරීම:

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

WITH clause භාවිතා කිරීම:

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

මෙම උදාහරණයේ, sales නමින් තාවකාලික ප්‍රතිඵල කට්ටලයක් WITH clause මඟින් නිර්වචනය කර, පසු ප්‍රධාන විමසුමේ යොමු කරයි. මෙය සම්පූර්ණ විමසුම වඩාත් තේරුම්ගත හැකි හා හොඳින් සංවිධානය කරයි.

Defining Multiple Common Table Expressions (CTEs)

WITH clause එකක් මත බහු CTEs නිර්වචනය කිරීමට ඉඩ සලසයි. මෙය සංකීර්ණ විමසුම් තවත් මොඩියුලර් කිරීමේ හැකියාව ලබා දෙයි.

උදාහරණය:

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

මෙම උදාහරණයේදී, sales_per_customer සෑම සාර්ථකයෙකු සඳහාම මුළු විකුණුම් ගණනය කරයි, සහ එම ප්‍රතිඵලය මත පදනම්ව, high_value_customers ඉහළ මිලදී ගැනීම් මුදල් ඇති සාර්ථකයන් නිස්සාරණය කරයි. බහු CTEs අනුපිළිවෙලින් භාවිතා කිරීමෙන්, පියවරෙන් පියවරට විමසුම් ගොඩනැගිය හැකිය.

Nested CTEs භාවිතා කිරීම

Nested CTEs භාවිතා කිරීමෙන්, ඔබට වඩා සංකීර්ණ දත්ත මෙහෙයුම් සිදු කළ හැකිය.

උදාහරණය:

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

මෙම විමසුමේදී, sales_data නිෂ්පාදනයකට අනුව විකුණුම් එකතුවෙයි, සහ ranked_sales විකුණුම් පරිමාණය මත පදනම්ව ශ්‍රේණිබේදනයන් ප්‍රකාශ කරයි. අවසානයේදී, ඉහළම පස් නිෂ්පාදන නිස්සාරණය කරයි.

ප්‍රායෝගික භාවිතය සඳහා ප්‍රධාන කරුණු

1. අභිරුචික පියවරවල සිතන්න CTEs පියවරෙන් පියවරට ගොඩනගන්නා ආකාරයෙන් පාඨකත්වය වැඩිදියුණු කරන්න සහ දෝෂ සෙවීම පහසු කරන්න.
2. මධ්‍යගත ගණනය කිරීම් ප්‍රතිඵල සංරක්ෂණය කරන්න බහුවිධ වරක් භාවිතා කරන ගණනය කිරීම් ප්‍රතිඵල හෝ සීරුම් තත්ත්වයන් CTEs වලට ගුටපෙල කරන්න කේත අනුපිටිකරණය අඩු කිරීමට.
3. විශාල දත්ත සමූහ සමඟ සැලකිලිමත් වන්න CTEs තාවකාලික ප්‍රතිඵල සමූහ ජනනය කරන බැවින්, විශාල දත්ත ප්‍රමාණ සමඟ වැඩ කිරීමේදී කාර්ය සාධනයට ඇති බලපෑම සලකා බලන්න.

4. Recursive WITH Clauses හි ප්‍රායෝගික උදාහරණ

Recursive WITH Clause යනු කුමක්ද?

Recursive WITH clause (recursive CTE) යනු Common Table Expression භාවිතා කරමින් ස්වයං-ආශ්‍රිත විමසුමක් නැවත නැවත ක්‍රියාත්මක කිරීමේ ක්‍රමයකි, එය hierarchical දත්ත සහ iterative ගණනයන් සැකසීමට ඔබට හැකි කරයි. Recursive CTEs MySQL 8.0 සහ පසුකාලීනව සහාය දක්වන අතර parent-child සම්බන්ධතා සහ hierarchical ව්‍යුහ සමඟ වැඩ කිරීමේදී විශේෂයෙන් යොදා ගත හැකිය.

Recursive CTE හි මූලික ව්‍යාකරණය

Recursive CTE අර්ථ දැක්වීමට, WITH RECURSIVE යන ගුණාංගය භාවිතා කරන්න. මූලික ව්‍යාකරණය පහත පරිදිය:

WITH RECURSIVE recursive_table_name AS (
  initial_query -- starting point of the recursion
  UNION ALL
  recursive_query -- query called recursively
)
SELECT * FROM recursive_table_name;

• මුල් විමසුම : recursive ක්‍රියාවලිය සඳහා පළමු දත්ත සමූහය ලබා ගනී.
• Recursive විමසුම : මුල් විමසුම හෝ පෙර පරිච්ඡේදයේ ප්‍රතිඵල මත පදනම්ව නව පේළි ජනනය කරයි.
• UNION ALL : මුල් විමසුම සහ recursive විමසුමේ ප්‍රතිඵල ඒකාබද්ධ කරයි.

උදාහරණය: Hierarchical දත්ත සැකසීම

Recursive CTEs සාමාන්‍යයෙන් hierarchical ව්‍යුහයක් ඇති දත්ත ව්‍යාප්ත කිරීමට (උදා: සංවිධාන වෘක්ෂ හෝ කාණ්ඩ වෘක්ෂ) භාවිතා වේ.

උදාහරණය: සේවක කළමනාකරණ hierarchical ව්‍යාප්ත කිරීම

පහත employees වගුව සලකන්න:

මෙම දත්ත භාවිතා කරමින්, දක්වා ලබා දී ඇති සේවකයෙකු සිට ආරම්භ වන පූර්ණ hierarchical ලබා ගන්නා විමසුමක් නිර්මාණය කළ හැකිය.

WITH RECURSIVE employee_hierarchy AS (
  -- Initial query: get top-level employees
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive query: get direct reports
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

ප්‍රතිඵලය:

මෙම විමසුමේදී, manager_id මත පදනම්ව අනුගාමිකයන් recursively සෙවීමෙන්, පූර්ණ hierarchical ව්‍යාප්ත කරයි.

Recursive CTEs සඳහා සීමාවන් සහ අනතුරු ඇඟවීම්

1. අවසාන තත්ත්වයක් අවශ්‍යයි Recursive විමසුම අවසාන තත්ත්වයක් සපුරාලන්නේ නම්, අනන්ත ලූපයක් සිදුවිය හැකිය. අනන්ත recursion වැළැක්වීමට සුදුසු තත්ත්වයන් ඇතුළත් කරන්න.
2. කාර්ය සාධනයට බලපෑම Recursive CTEs විශාල දත්ත සමූහ මත බොහෝ ගණනයන් සමාවිෂ්ට කළ හැකි බැවින්, ක්‍රියාකාරීත්ව කාලය වැඩි විය හැකිය. කාර්යක්ෂමතාව වැඩිදියුණු කිරීමට LIMIT clauses සහ සීරුම් තත්ත්වයන් භාවිතා කරන්න.
3. Recursion ගැඹුර සීමා MySQL හි recursion ගැඹුර සීමාවක් ඇති බැවින්, ඉතා ගැඹුරු recursive සැකසුම් ක්‍රියාත්මක කිරීමේදී සැලකිලිමත් වන්න. මෙම සීමාව max_recursive_iterations පරාමිතියෙන් සකස් කළ හැකිය.

Recursive CTEs ප්‍රයෝජනවත් වන අවස්ථා

• ෆෝල්ඩර් ව්‍යුහයන් ගමන් කිරීම : ෆෝල්ඩර් සහ උප-ෆෝල්ඩර් සෙවීම රිකර්සිවී.
• ආයතන චාට් සෑදීම : කළමනාකරුවන් සිට උපකාරකයන් දක්වා පිරිවිතර පෙන්වීම.
• ප්‍රවර්ග ගස පෙන්වීම : අනුක්‍රමික නිෂ්පාදන ප්‍රවර්ග හෝ ටැග් ව්‍යුහයන් ලබා ගැනීම.

Recursive CTEs මෙම අවස්ථා සඳහා කෙටි SQL විමසුම් ලිවීමට ශක්තිමත් ක්‍රමයක් වන අතර කියවීමට පහසුකම වැඩි කරයි.

5. WITH වාක්‍යය භාවිතා කිරීමේ සටහන් සහ සැලකිලිමත් කරුණු

කාර්ය සාධන බලපෑම සහ සුදුසුකම්

1. CTE නැවත ගණනය කිරීම සාමාන්‍යයෙන්, WITH වාක්‍යය සමඟ නිර්වචනය කරන CTE එක එය යොමු කරන සෑම වරක්ම නැවත ගණනය කරයි. එබැවින්, එකම CTE එක බහු වරක් භාවිතා කිරීම විමසුම් ක්‍රියාකාලය වැඩි කරයි. උදාහරණය:

   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;
   

ඉහත අවස්ථාවේ, sales දෙවරක් යොමු කර ඇත, එබැවින් එය දෙවරක් ගණනය වේ. මෙය වැළැක්වීමට, ඔබට ප්‍රතිඵල බහු වරක් යොමු කිරීමට අවශ්‍ය නම්, ප්‍රතිඵලය තාවකාලික වගුවක සුරකින්නා වඩා ප්‍රයෝජනවත් වේ.

විසඳුම:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;

SELECT * FROM temp_sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM temp_sales;

2. සංකීර්ණ CTE ගොඩනැගීම වෙන් කිරීම WITH වාක්‍යය ඉතා ගැඹුරු ලෙස අන්තර්ගත වුවහොත්, මුළු විමසුම සංකීර්ණ වන අතර දෝෂ නිරාකරණය කිරීමට අමාරු වේ. එක් CTE එකක් තුළ ප්‍රකාරය අධික ලෙස සංකීර්ණ නොවීමට, තර්කය සුදුසු ලෙස වෙන් කිරීම වැදගත් වේ.

විශාල දත්ත කට්ටලයන්හි WITH වාක්‍ය භාවිතය

WITH වාක්‍යය ක්‍රියාත්මක වන විට තාවකාලික දත්ත කට්ටලයන් ජනනය කරයි. විශාල ප්‍රමාණයේ දත්ත සැකසීමේදී, මෙය මතකය හෝ ගබඩා කිරීමේ බරක් විය හැක.

ප්‍රතිකාර:

• WHERE වාක්‍යයන් සමඟ දත්ත පෙරහන් කිරීම CTE තුළ අවශ්‍ය නොවන දත්ත පෙරහන් කිරීමෙන් ගණනය අඩු කරයි.

  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;
  

• LIMIT වාක්‍යයන් භාවිතා කිරීම දත්ත කට්ටලය විශාල නම්, ඔබට අවශ්‍ය දත්ත පමණක් ලබා ගැනීමට LIMIT භාවිතා කරන්න.

MySQL අනුවාද අනුකූලතාව

MySQL හි WITH වාක්‍යය MySQL 8.0 සහ ඊට පසු අනුවාද වලට සහාය දක්වයි. පූර්ව අනුවාද වල WITH වාක්‍යය නොමැති බැවින්, විකල්පයන් ගැන සිතිය යුතුය.

විකල්ප:

• උපවිමසුම් භාවිතා කිරීම WITH වාක්‍යය වෙනුවට උපවිමසුම් සෘජුව භාවිතා කරන්න.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• දෘශ්‍යයන් නිර්මාණය ඔබට නැවත භාවිතා කළ හැකි විමසුම් අවශ්‍ය නම්, දෘශ්‍යයක් භාවිතා කිරීම ප්‍රයෝජනවත් වේ.

  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM sales_view;
  

WITH වාක්‍යය නිසි ලෙස භාවිතා කිරීම

1. කියවීමට පහසුකම ප්‍රමුඛ කරගන්න WITH වාක්‍යයේ අරමුණ විමසුම් සංවිධානය කිරීම සහ කියවීමට පහසුකම වැඩි කිරීමයි. එය අධික ලෙස භාවිතා කිරීම විමසුම් සංකීර්ණ කරයි, එබැවින් අවශ්‍ය විට පමණක් භාවිතා කරන්න.
2. කාර්ය සාධනය තහවුරු කරන්න ක්‍රියාත්මක සැලැස්ම (EXPLAIN විධානය) පරීක්ෂා කර කාර්ය සාධනය සුදුසු කර ගැනීමට සිතන්න.

   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   

6. වාස්තුකාලීන අවස්ථා තුළ ප්‍රායෝගික භාවිතා

විකුණුම් දත්ත එකතු කිරීම

මාස අනුව විකුණුම් දත්ත එකතු කිරීමේ උදාහරණයක් මෙහි ඇත, එම ප්‍රතිඵලය භාවිතා කර මාසික සාමාන්‍ය විකුණුම් ගණනය කිරීම.

උදාහරණය: මාසික විකුණුම් එකතු කිරීම සහ සාමාන්‍යය ගණනය කිරීම

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

මෙම විමසුමේදී, monthly_sales මාසික විකුණුම් ගණනය කරයි, සහ එම ප්‍රතිඵල මත පදනම්ව සමස්ත සාමාන්‍ය විකුණුම් ගණනය කෙරේ. මෙය දත්ත පැහැදිලිව සංවිධානය කිරීමට සහ විශ්ලේෂණය සරල කිරීමට ඉඩ සලසයි.

විශේෂ තත්ත්ව මත පදනම්ව දත්ත සෙවීම

සංකීර්ණ සෙවීම් තර්කය WITH කොටුවකට වෙන් කිරීමෙන් කියවීමේ හැකියාව වැඩි දියුණු කළ හැක.

උදාහරණය: ඉහළ වැය කරන පාරිභෝගිකයින්ගේ ලැයිස්තුවක් සෑදීම

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

මෙම විමසුමේදී, customer_totals පාරිභෝගිකයෙකුගේ මුළු මිලදී ගැනීමේ මුදල ගණනය කරයි, සහ නිර්දිෂ්ට තත්ත්වය සපුරාලන පාරිභෝගිකයින් නිගමනය කෙරේ.

අනුපිළිවෙල් දත්ත විශ්ලේෂණය

සංවිධාන ව්‍යුහයන් හෝ කාණ්ඩ වැනි අනුපිළිවෙල් දත්ත විශ්ලේෂණය කිරීමේදී, පුනරාවර්තන WITH කොටු ඉතා උපකාරී වේ.

උදාහරණය: සෘජු සහ වක්‍ර අනුබද්ධිකයින්ගේ ලැයිස්තුවක් ලබා ගැනීම

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

මෙම විමසුම අනුපිළිවෙල් දත්ත employee_hierarchy හි ගොඩනඟයි සහ මට්ටම් අනුව කණ්ඩායම් කළ සේවකයින් ලබා ගනී. එය සංවිධාන සටහනක් වැනි තොරතුරු ගතිකව ජනනය කිරීමට ඉඩ සලසයි.

බහු CTE භාවිතයෙන් උසස් විශ්ලේෂණය

බහු WITH කොටු භාවිතයෙන් දත්ත පියවරෙන් පියවර ප්‍රතිකාරණය කළ හැකි අතර, සංකීර්ණ විශ්ලේෂණය සරල කරයි.

උදාහරණය: කාණ්ඩයකට අනුව වැඩිම විකුණුම් ලබන නිෂ්පාදන තෝරා ගැනීම

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

මෙම විමසුමේදී, කාණ්ඩයකට අනුව විකුණුම් ගණනය කරනු ලැබේ, සහ එක් එක් කාණ්ඩය තුළ ඉහළම තුනේ නිෂ්පාදන තෝරා ගනී. නිශ්චිත තත්ත්ව මත පදනම්ව දත්ත සීමා කිරීමේදී මෙම ප්‍රවේශය ඵලදායී වේ.

ප්‍රායෝගික යෙදීම සඳහා ප්‍රධාන කරුණු

1. විමසුම් තර්කානුකූල අදියරවල සැලසුම් කිරීම WITH කොටුව භාවිතයෙන් විමසුම් බෙදා දත්ත පියවරෙන් පියවර ප්‍රතිකාරණය කරන්න, කියවීමේ හැකියාව රැකගෙන.
2. අවශ්‍ය දත්ත පමණක් නිගමනය කිරීම අනවශ්‍ය දත්ත ප්‍රතිකාරණය වළක්වා ගැනීමට WHERE සහ LIMIT කොටු භාවිතා කරන්න සහ ඵලදායී විමසුම් සැලසුම් කරන්න.
3. නම්‍යශීලී ව්‍යාපාර යෙදුම් WITH කොටුව විකුණුම් විශ්ලේෂණය, පාරිභෝගික කොටස්කිරීම, ඉන්වෙන්ටරි කළමනාකරණය සහ තවත් බොහෝ දේ සඳහා නම්‍යශීලීව යෙදිය හැක.

7. FAQ (පොදු ප්‍රශ්න)

Q1: WITH කොටුව කවදා භාවිතා කළ යුතුද?

A1:
WITH කොටුව පහත දර්ශනවලදී විශේෂයෙන් ඵලදායී වේ:

• සංකීර්ණ උපවිමසුම් සරල කිරීමට ඔබට අවශ්‍ය වන විට.
• විමසුමක් තුළ එකම දත්ත සෙට් බහුවරණයෙන් භාවිතා කිරීමට ඔබට අවශ්‍ය වන විට.
• කියවීමේ හැකියාව වැඩි දියුණු කිරීමට විමසුමක් තර්කානුකූලව බෙදා ගැනීමට ඔබට අවශ්‍ය වන විට.

උදාහරණයක් ලෙස, එකම එකතුවීමේ ප්‍රතිඵල බහුවරණයෙන් භාවිතා කරන විමසුම්වලදී, WITH කොටුව වඩාත් කාර්යක්ෂම සංවිධානයට ඉඩ සලසයි.

Q2: පුනරාවර්තන CTE එක කවදා උපකාරීද?

A2:
පුනරාවර්තන CTE ගුණාංග ව්‍යුහයන් හෝ පුනරාවර්තන ගණනයන් කළමනාකරණය කිරීමේදී උපකාරී වේ. විශේෂයෙන්:

• අනුපිළිවෙල් දත්ත ප්‍රතිකාරණය (උදා: සංවිධාන ගස්, කාණ්ඩ ව්‍යුහයන්).
• ෆෝල්ඩර් හෝ ෆයිල් අනුපිළිවෙල් පෙන්වීම .
• අංක හෝ කාල පරාසවල අනුපිළිවෙල් ගණනයන් (උදා: ෆිබනාචි අනුපිළිවෙල ගණනය කිරීම).

පුනරාවර්තන CTE භාවිතයෙන් ස්වයං-ආශ්‍රිත දත්ත ව්‍යාප්ත කිරීම සහ ප්‍රතිකාරණය කිරීම පහසු වේ.

Q3: WITH වාක්‍යය භාවිතා කරන විමසුම් දෘශ්‍යයන්ට වඩා වැඩි කාර්යක්ෂමද?

A3:
එය භාවිතා කරන අවස්ථාව මත පදනම් වේ.

• WITH වාක්‍යය : විමසුමේ ඇතුළත පමණක් භාවිතා වන තාවකාලික ප්‍රතිඵල කට්ටලයක් සෑදේ. නිතර නැවත භාවිතා කිරීම අවශ්‍ය නොවන දත්ත සඳහා සුදුසුය.
• දෘශ්‍යය : දත්ත ගබඩාවේ ස්ථිරව සුරැකුම් කර ඇති අතර අනෙකුත් විමසුම් මගින් නැවත භාවිතා කළ හැක. නිතර භාවිතා වන විමසුම් සඳහා සුදුසුය.

අවස්ථාව අනුව සුදුසු ක්‍රමය තෝරා ගැනීම වැදගත් වේ.

Q4: WITH වාක්‍යය භාවිතා කරන විට කාර්ය සාධන අඩු වීමේ හේතු මොනවාද?

A4:
WITH වාක්‍යය භාවිතා කරන විට කාර්ය සාධන අඩු වීමේ ප්‍රධාන හේතු පහත පරිදි වේ:

• CTE නැවත ගණනය කිරීම : ප්‍රතිඵලය සෑම වරක්ම යොමු කරන විට නැවත ගණනය කරයි, මෙය සැකසුම් කාලය වැඩි කරයි.
• විශාල දත්ත කට්ටලයන් සැකසීම : CTE තුළ විශාල දත්ත කට්ටලයන් ජනනය කිරීම මතක භාවිතය වැඩි කරයි සහ කාර්ය සාධනය අඩු කරයි.
• සුදුසු දර්ශක නොමැති වීම : CTE තුළ ඇති විමසුම් සුදුසු දර්ශක භාවිතා නොකළහොත්, කාර්ය සාධනය මන්දගාමී විය හැක.

ප්‍රතිකාර:

• නැවත භාවිතා කිරීමේ අඩිතාලය ඉහළ නම් තාවකාලික වගු හෝ දෘශ්‍යයන් ගැන සිතන්න.
• දත්ත නිවැරදිව සීමා කිරීම සඳහා WHERE සහ LIMIT වාක්‍යයන් භාවිතා කරන්න.

Q5: WITH වාක්‍යය සහය නොදක්වන MySQL අනුවාද සඳහා වෙනත් මාර්ග මොනවාද?

A5:
MySQL 8.0 ට පෙර අනුවාදවල WITH වාක්‍යය සහය නොදක්වන බැවින්, පහත විකල්ප භාවිතා කරන්න:

• උපවිමසුම් භාවිතා කරන්න WITH වාක්‍යය වෙනුවට උපවිමසුම් සෘජුව භාවිතා කරන්න.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• තාවකාලික වගු භාවිතා කරන්න නැවත භාවිතා කළ හැකි දත්ත කට්ටලයන් තාවකාලික වගුවක සුරකින්න.

  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM temp_sales;
  

Q6: WITH වාක්‍යය භාවිතා කරන විට හොඳම ක්‍රියාමාර්ග මොනවාද?

A6:
පහත හොඳම ක්‍රියාමාර්ග මතක තබා ගන්න:

• සරලතාවයට ප්‍රමුඛත්වය දෙන්න : සංකීර්ණ තර්කයක් එක WITH වාක්‍යයකට බලපෑම නොකරන්න. එය සුදුසු ලෙස බෙදන්න.
• කාර්ය සාධනය තහවුරු කරන්න : EXPLAIN විධානය භාවිතා කර ක්‍රියාත්මක සැලැස්ම පරීක්ෂා කර අවශ්‍ය පරිදි සුදානම් කරන්න.
• නැවත භාවිතා කිරීම ගැන සිතන්න : නැවත භාවිතා කිරීමේ අඩිතාලය ඉහළ නම්, දෘශ්‍යයන් හෝ තාවකාලික වගු භාවිතා කරන්න.

8. නිගමනය

මෙම ලිපිය MySQL 8.0 හි හඳුන්වා දුන් WITH වාක්‍යය (සාමාන්‍ය වගු ප්‍රකාශනය, CTE) පිළිබඳ මූලික කරුණු සිට උසස් යෙදුම් දක්වා ආවරණය කරයි. සංකීර්ණ විමසුම් කෙටි හා කියවීමට පහසු කර ගැනීමට WITH වාක්‍යය ඉතා ප්‍රයෝජනවත් ලක්ෂණයක් වේ. පහත ප්‍රධාන කරුණු සාරාංශය වේ.

WITH වාක්‍යයේ ප්‍රධාන ප්‍රතිලාභ

1. විමසුම් කියවීමේ හැකියාව වැඩි කිරීම සංකීර්ණ උපවිමසුම් සංවිධානය කර, SQL කේතයේ කියවීමේ හැකියාව සහ නඩත්තු කිරීමේ හැකියාව වැඩි කරයි.
2. විමසුම් නැවත භාවිතා කිරීම එකම දත්ත කට්ටලය බහු වරක් යොමු කරන විට දත්ත කාර්යක්ෂමව සැකසීමට හැකියාව ලබා දෙයි.
3. ප්‍රතිචක්‍රාත්මක දත්ත මෙහෙයුම් සක්‍රිය කිරීම ප්‍රතිචක්‍රාත්මක CTEs හරහා ශ්‍රේණිගත දත්ත සහ පනස්කාර ගණනයන් සැකසීම සරල කරයි.

ප්‍රායෝගික භාවිතා කරුණු

• විකුණුම් සහ ගනුදෙනුකරුවන්ගේ දත්ත විශ්ලේෂණ සඳහා ප්‍රයෝජනවත්, පියවරෙන් පියවර එකතු කිරීම සක්‍රිය කරයි.
• ප්‍රතිචක්‍රාත්මක CTEs ශ්‍රේණිගත දත්ත සැකසීම සඳහා (උදා: සංවිධාන රූප සටහන් හෝ ප්‍රවර්ග ව්‍යුහ) ප්‍රයෝජනවත් වේ.
• WITH වාක්‍යයන් දෘශ්‍යයන් හෝ තාවකාලික වගු සමඟ එකතු කිරීමෙන් ලවච්ච සහ කාර්යක්ෂම දත්ත ගබඩා මෙහෙයුම් සිදු කළ හැක.

වැදගත් සැලකිලි

• WITH වාක්‍යය ශක්තිමත් වුවද, අනිසි භාවිතය කාර්ය සාධනය අඩු කරයි.
• නැවත භාවිතා කිරීම සහ කාර්ය සාධනය තත්ත්ව අනුව ඇගයී, අවශ්‍ය විට දෘශ්‍යයන් හෝ තාවකාලික වගු අතර තේරීම කරන්න.
• සෑම විටම ක්‍රියාත්මක සැලැස්ම (EXPLAIN විධානය) භාවිතා කර විමසුම් කාර්යක්ෂමතාව තහවුරු කරන්න.

ඊළඟ පියවර

WITH වාක්‍යය භාවිතා කිරීමෙන් ඔබට වඩා කාර්යක්ෂම සහ නඩත්තු කළ හැකි SQL විමසුම් සෑදිය හැක. මෙම පියවර අනුගමනය කර ඔබේ ව්‍යාපාරික ව්‍යාපෘති වලට එය යොදා බලන්න:

1. සරල විමසුම් වලින් ආරම්භ කර WITH වාක්‍යය භාවිතා කර ඒවා සංවිධානය කිරීම පුහුණු කරන්න.
2. ශ්‍රේණිගත දත්ත සහ සංකීර්ණ අවස්ථා සඳහා ප්‍රතිචක්‍රාත්මක CTEs භාවිතා කර ඔබේ හැකියාව පරීක්ෂා කරන්න.
3. කාර්ය සාධන සුදානමට අවධානය යොමු කර ඔබේ SQL කුසලතා තවත් වැඩිදියුණු කරන්න.

මෙම ලිපිය අවසන් වේ. ඔබේ MySQL WITH වාක්‍යය පිළිබඳ දැනුම ඔබේ දෛනික වැඩ සහ අධ්‍යයන වල භාවිතා කරන්න.