As discussed in the previous post, MySQL is supporting “Window functions” beginning with version 8.0. The window functions enable you to tackle query problems in new, easier, and faster ways. The following are important considerations to remember.
The following is an example dataset for practice.
CREATE TABLE tbProducts(
ProductID INT,
ProductCategory VARCHAR(200),
Manufacturer VARCHAR(200),
ProductName VARCHAR(200),
ProductDescription VARCHAR(200),
PricePerItem DECIMAL(10,2),
UnitSales INT);
INSERT INTO tbProducts VALUES
(1001, 'Desktop', 'Dell', 'Dell OptiPlex 3080', 'Core i5 10500 3.1 GHz - 8 GB - SSD 512', 1658, 100),
(1002, 'Desktop', 'Dell', 'Dell Optiplex Intel Core i7-3770','16GB DDR3-512GB',450,88),
(1003, 'Desktop', 'Dell', 'Dell Desktop PC Tower 7080', '10th Gen Intel Core i7-10700, 32GB DDR4, 1TB Hdd+',2765,78),
(1004, 'Desktop', 'HP', 'HP Elite 8300 SFF Quad Core i7-3770', '3.4GHz 16GB 256GB SSD + 1000GB HDD DVD WiFi Windows',394, 99),
(1005, 'Desktop', 'HP', 'HP EliteDesk 800 G1 SFF Black Desktop PC Intel Quad Core i5-4570', '3.20GHz, 8GB RAM, 256GB SSD',323, 110),
(1006, 'Desktop', 'HP', 'HP ProDesk 600 G1 ','SFF Slim Business Desktop Computer, Intel i5-4570 up to 3.60 GHz, 8GB RAM',316,230),
(1007, 'Desktop', 'Dell', 'Dell OptiPlex 3020 SFF 4th Gen Core i5-4590', '8GB 240GB SSD DVDRW',338, 120),
(1008, 'Desktop', 'Lenovo', 'Lenovo ThinkCentre High Performance M73 SFF Desktop Computer','Intel Core i3 Processor 3.4GHz 8GB RAM 512GB SSD DVD',1899, 230),
(1009, 'Desktop', 'HP', 'HP Compaq Prodesk 6200 Pro Slim Business Desktop Computer','Small Form Factor (SFF), Intel i5-2400 up to 3.4GHz, 8GB DDR3',398, 200),
(1010, 'Desktop', 'Dell', 'Dell Inspiron 3910 Desktop (2022)', 'Core i5-512GB SSD - 8GB RAM | 6 Cores @ 4.4 GHz - 12th Gen CPU Win 11 Home',1907,100),
(1011, 'Desktop', 'Lenovo', 'Lenovo Desktop M91p SFF Core i7-2600','3.40GHz 8GB 500GB HDD Win 10 Pro (Renewed)',490, 123),
(1012, 'Laptop', 'Dell', 'Dell Latitude 7480 Laptop Pc','14 Fhd (1920X1080) Non-Touch, Intel I5 2.60Ghz Processor, 16 Gb Ram Ddr4, 512 Gb Nvme',702, 230),
(1013, 'Laptop', 'HP', '2022 HP 15.6 FHD Laptop Computer', '11th Gen Intel Core i5 1135G7 Beats Intel i7 1065G7, 16GB RAM, 1TB SSD, Intel Iris X',1875, 320),
(1014, 'Laptop', 'Lenovo', 'Lenovo 2022 Newest IdeaPad 14" FHD IPS Laptop Computer', 'Intel Core i5-10210U, Quad Core Upto 4.2 GHz, 12GB RAM, 512GB PCIe',1645, 100),
(1015, 'Laptop', 'HP', 'HP 2022 Newest HP 15.6in FHD 1080P IPS Display Laptop Computer', '11th Gen Intel Quad-Core i5-1135G7 Upto 4.2GHz',1905, 200),
(1016, 'Laptop', 'HP','HP 15 Business Laptop Computer', '11th Gen Intel Core i5-1135G7, 15.6" FHD IPS Display, Windows 11 Pro, 16GB RAM, 512GB SSD,',1900, 120),
(1017, 'Laptop', 'Acer', 'Acer Nitro 5 AN515 Gaming Notebook', '11th Gen Intel Core i9-119000H Octa Core 2.50GHz Upto 4.90GHz/16GB',4299, 145),
(1018, 'Laptop', 'Acer', 'Acer Predator Helios 300 PH315-55-70ZV Laptop Computer (2022)', 'Intel i7-12700H | NVIDIA GeForce RTX 3060 GPU | 15.6"',5013, 10),
(1019, 'Laptop', 'Acer', 'Acer Aspire 5 15.6" FHD 1080P Laptop Computer', 'AMD Ryzen 3 3200U (Beat i5-7200u) 8GB RAM 256GB SSD',2099, 10),
(1020, 'Laptop', 'Acer', 'Acer Aspire 3 Slim Laptop Computer', '15.6 inches Full HD Display, Intel Quad-Core i5-1035G1 (Up to 3.6 GHz), 12GB',2058, 18);
SELECT
DISTINCT ProductCategory, Manufacturer,
SUM(PricePerItem * UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) AS Manufacturer_TotalSales,
AVG(PricePerItem * UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory) AS Manufacturer_AVGSales,
MAX(UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory)
AS HighestSalesPerCategry,
MIN(UnitSales) OVER(PARTITION BY Manufacturer, ProductCategory)
AS LowestSalesPerCategry
FROM tbProducts
ORDER BY Manufacturer;
Output is -
Laptop Acer 731519.00 182879.750000 145 10
Desktop Dell 652330.00 130466.000000 120 78
Laptop Dell 161460.00 161460.000000 230 230
Desktop HP 226816.00 56704.000000 230 99
Laptop HP 1209000.00 403000.000000 320 120
Desktop Lenovo 497040.00 248520.000000 230 123
Laptop Lenovo 164500.00 164500.000000 100 100
Big Data and SQL 
One comment