Với bài tập lần trước các anh chị đã làm quen với sub query và with CTE. Tuy nhiên với subquery hay CTE chúng ta chỉ sử dụng kết quả đó trong một lần thực thi câu query vậy câu hỏi đặt ra có cách nào dùng lại các kết quả query nhiều lần trong 1 session không?

-- sub query with exists
select  LastName, FirstName
from Person.Person P
where exists (select 1
			from HumanResources.EmployeePayHistory EP
			where P.BusinessEntityID = EP.BusinessEntityID
			and EP.Rate > 30)

-- with cte
with salary_gt_30 as (
    select distinct EP.BusinessEntityID
    from HumanResources.EmployeePayHistory EP
    where  EP.Rate > 30
)

select  
    LastName
    ,FirstName
from Person.Person P inner join salary_gt_30 gt30 on P.BusinessEntityID = gt30.BusinessEntityID

-- temp table
select distinct 
    EP.BusinessEntityID
    ,EP.rate
into #temp_gt_30_salary
from HumanResources.EmployeePayHistory EP
where  EP.Rate > 30


select  LastName, FirstName
from Person.Person P inner join #temp_gt_30_salary gt30 on P.BusinessEntityID = gt30.BusinessEntityID

Tại sao kết quả của bảng tạm lại lớn hơn của 2 câu trên? Tìm cách khắc phục

-- temp table
-- delete the temp table and insert again
drop TABLE if exists #temp_gt_30_salary

select distinct 
    EP.BusinessEntityID
    ,max(EP.rate) as rate
into #temp_gt_30_salary
from HumanResources.EmployeePayHistory EP
where  EP.Rate > 30
group by EP.BusinessEntityID

Window function

chúng ta có bảng [Sales].[SalesPersonQuotaHistory] chứa thông tin doanh thu theo từng năm của từng nhân viên. Hãy tìm ra best salers của mỗi năm và doanh thu của họ

đáp án

đầu tiên ta cần tính tổng quota của từng nhân viên theo năm

with sum_quota_by_year as(
select 
    BusinessEntityID
    ,YEAR(QuotaDate) as [year_quota]
    ,sum([SalesQuota]) as sale_quota
    from  [Sales].[SalesPersonQuotaHistory]
    group by YEAR(QuotaDate), BusinessEntityID
)

select * from sum_quota_by_year

Sau đó ta cần tìm ra chỉ số sale_quota lớn nhất theo năm dựa theo số tổng

with sum_quota_by_year as(
select 
    BusinessEntityID
    ,YEAR(QuotaDate) as [year_quota]
    ,sum([SalesQuota]) as sale_quota
    from  [Sales].[SalesPersonQuotaHistory]
    group by YEAR(QuotaDate), BusinessEntityID
)
select   year_quota
        ,max(sale_quota) as max_sale_quota_by_year
from sum_quota_by_year
group by year_quota

ta dùng lại 2 bảng kết quả join lại với nhau theo 2 cột YEAR_QUOTA VÀ SALES

with sum_quota_by_year as(
select 
    BusinessEntityID
    ,YEAR(QuotaDate) as [year_quota]
    ,sum([SalesQuota]) as sale_quota
    from  [Sales].[SalesPersonQuotaHistory]
    group by YEAR(QuotaDate), BusinessEntityID
),
 year_max_sale as 
(
    select   year_quota
            ,max(sale_quota) as max_sale_quota
    from sum_quota_by_year
    group by year_quota
)
select a.*
FROM sum_quota_by_year A inner join year_max_sale B 
    on  A.year_quota = B.year_quota 
        and A.sale_quota = b.max_sale_quota

Có cách nào nhanh hơn cách trên không?

tại sao không xếp hạng số sum của SalesQuota từng BusinessEntityID theo từng năm. Sau đó ta sẽ lấy ra các sales có hạng 1

SELECT
    [BusinessEntityID] AS SalesID,
    YEAR([QuotaDate]) AS SalesYear,
    SUM([SalesQuota]) AS TotalSalesQuota,
    DENSE_RANK() OVER (PARTITION BY YEAR([QuotaDate]) ORDER BY SUM([SalesQuota]) DESC) AS SalesRank
FROM [AdventureWorks2017].[Sales].[SalesPersonQuotaHistory]
    SalesQuotaTable
GROUP BY
    [BusinessEntityID],
    YEAR([QuotaDate])
ORDER BY
    YEAR([QuotaDate]), SalesRank;

Cú pháp

<Function> : đây là hàm xếp hạng
OVER : đây là mệnh đề của windows functions
PARTITION BY<cloumn_name>: nhóm để xếp hạng
ORDER BY <column_name> ASC | DESC : sắp xếp 

Dựa vào bảng [HumanResources].[Employee] và HumanResources].[EmployeePayHistory] tìm ra tên và sắp sếp theo ranking mức lương trung bình của jobtitle "Production Technician - WC30"

select 
    E.JobTitle
    , E.BusinessEntityID
    , avg(Rate) as avg_rate
    , DENSE_RANK() over (partition by E.JobTitle order by avg(Rate) desc )
  from [HumanResources].[Employee] E inner join [HumanResources].[EmployeePayHistory] EP 
        on E.BusinessEntityID = EP.BusinessEntityID
where E.JobTitle = 'Production Technician - WC30'
group by E.JobTitle
    , E.BusinessEntityID

Bài tập

1. Ranking Products by Sales:
   Write a SQL query to rank products based on their total sales amount in descending order. Include the product name, total sales amount, and the corresponding rank for each product.

2. Monthly Sales Growth:
   Calculate the monthly sales growth for each product category. Display the product category, the month, and the percentage growth compared to the previous month. Order the results by category and month.

3. Running Total of Orders:
   Calculate the running total of orders for each day in the order table. Display the order date, the number of orders on that date, and the running total of orders up to that date.

4. Top 5 Customers by Total Revenue:
   Find the top 5 customers who have generated the highest total revenue. Display their customer names, customer IDs, and the total revenue they have contributed.

5. Calculating Percentile:
   Calculate the 25th, 50th, and 75th percentile of the total sales amount for each product category. Display the category name and the respective percentile values.

6. Sales Contribution Ratio:
   Determine the sales contribution ratio of each product within its category. The sales contribution ratio of a product is the percentage of the product's sales amount compared to the total sales amount of its category. Display the product name, sales amount, category name, and the contribution ratio.

7. Top 3 Products per Category:
   Find the top 3 products with the highest sales amount for each product category. Display the category name, product name, and sales amount for each product.

8. Lag and Lead Calculation:
   Calculate the lag and lead of the sales amount for each product. The lag represents the previous sales amount, and the lead represents the next sales amount. Display the product name, sales amount, lag, and lead.