Advertisement
hadimaster65555

SQL - Window Function - Value Function Script by HadiMaster

Apr 3rd, 2024
527
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 3.73 KB | Source Code | 0 0
  1. CREATE TABLE sales_data (
  2.     product_id SERIAL PRIMARY KEY,
  3.     product_name VARCHAR(50),
  4.     price DECIMAL(10, 2),
  5.     product_group VARCHAR(20)
  6. );
  7.  
  8. INSERT INTO sales_data (product_name, price, product_group) VALUES
  9. ('Product A', 100.00, 'Group 1'),
  10. ('Product B', 150.00, 'Group 2'),
  11. ('Product C', 80.00, 'Group 1'),
  12. ('Product D', 120.00, 'Group 3'),
  13. ('Product E', 200.00, 'Group 2'),
  14. ('Product F', 90.00, 'Group 1'),
  15. ('Product G', 110.00, 'Group 3'),
  16. ('Product H', 130.00, 'Group 4'),
  17. ('Product I', 95.00, 'Group 1'),
  18. ('Product J', 180.00, 'Group 2'),
  19. ('Product K', 85.00, 'Group 1'),
  20. ('Product L', 115.00, 'Group 3'),
  21. ('Product M', 210.00, 'Group 2'),
  22. ('Product N', 70.00, 'Group 1'),
  23. ('Product O', 125.00, 'Group 4'),
  24. ('Product P', 100.00, 'Group 1'),
  25. ('Product Q', 160.00, 'Group 2'),
  26. ('Product R', 75.00, 'Group 1'),
  27. ('Product S', 105.00, 'Group 3'),
  28. ('Product T', 220.00, 'Group 2'),
  29. ('Product U', 65.00, 'Group 1'),
  30. ('Product V', 135.00, 'Group 4'),
  31. ('Product W', 110.00, 'Group 1'),
  32. ('Product X', 170.00, 'Group 2'),
  33. ('Product Y', 80.00, 'Group 1'),
  34. ('Product Z', 95.00, 'Group 3'),
  35. ('Product AA', 230.00, 'Group 2'),
  36. ('Product BB', 60.00, 'Group 1'),
  37. ('Product CC', 140.00, 'Group 4');
  38.  
  39. -- melihat keseluruhan data
  40. SELECT * FROM sales_data;
  41.  
  42. -- find cheapest product from all product based on its price
  43. SELECT
  44.     product_id,
  45.     product_name,
  46.     price,
  47.     product_group,
  48.     FIRST_VALUE(product_name) OVER (ORDER BY price) AS cheapest_product
  49. FROM
  50.     sales_data;
  51.  
  52. -- find cheapest product based on each group sorted by its price
  53. SELECT
  54.     product_id,
  55.     product_name,
  56.     price,
  57.     product_group,
  58.     FIRST_VALUE(product_name) OVER (PARTITION BY product_group ORDER BY price) AS cheapest_product
  59. FROM
  60.     sales_data;
  61.    
  62. -- find priciest product from all product based on its price
  63. SELECT
  64.     product_id,
  65.     product_name,
  66.     price,
  67.     product_group,
  68.     LAST_VALUE(product_name) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS priciest_product
  69. FROM
  70.     sales_data;
  71.  
  72. -- find product based on its sorted price then pick the last product name
  73. SELECT
  74.     product_id,
  75.     product_name,
  76.     price,
  77.     product_group,
  78.     LAST_VALUE(product_name) OVER (ORDER BY price ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS last_product
  79. FROM
  80.     sales_data;
  81.  
  82. -- find priciest product for every group sorted by its price
  83. SELECT
  84.     product_id,
  85.     product_name,
  86.     price,
  87.     product_group,
  88.     LAST_VALUE(product_name) OVER (PARTITION BY product_group ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS priciest_product
  89. FROM
  90.     sales_data;
  91.  
  92. -- find 1 lag of product name for every product group sorted by its price
  93. SELECT
  94.     product_id,
  95.     product_name,
  96.     price,
  97.     product_group,
  98.     LAG(product_name) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
  99. FROM
  100.     sales_data;
  101.  
  102. -- find 2 lag of product name for every product group sorted by its price
  103. SELECT
  104.     product_id,
  105.     product_name,
  106.     price,
  107.     product_group,
  108.     LAG(product_name, 2) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
  109. FROM
  110.     sales_data;
  111.  
  112. -- find 1 lead of product name for every product group sorted by its price
  113. SELECT
  114.     product_id,
  115.     product_name,
  116.     price,
  117.     product_group,
  118.     LEAD(product_name) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
  119. FROM
  120.     sales_data;
  121.  
  122. -- find 2 lead of product name for every product group sorted by its price
  123. SELECT
  124.     product_id,
  125.     product_name,
  126.     price,
  127.     product_group,
  128.     LEAD(product_name,2) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
  129. FROM
  130.     sales_data;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement