按日期汇总交易数据并轧差的SQL实现需求咨询

Great question—let's break down how your current query aligns with your requirements and what improvements we can make to make it robust, accurate, and maintainable.

Key Issues with the Current Query #

Your core logic has some gaps that prevent it from meeting your exact needs:

1. Missing GROUP BY Clause: The biggest issue is that you're not grouping by value_date (the Date field in your requirement). Without this, your query will return a single aggregated row for the entire date range instead of daily statistics. That's why you won't get the per-date breakdown you want.
2. Incorrect Sale Transaction Logic: You're using tran_type NOT IN (...) to calculate sales, which would include any transaction type not in your purchase list—even if those aren't actual sales (like adjustments, transfers, etc.). Per your requirement, sales should only include explicit types like SAL, SALF, etc., not everything else.
3. Net Purchase Includes Non-Relevant Transactions: Your current Net_Purchase calculation subtracts the value of all non-purchase transactions, including those that aren't sales. This will skew your net amount if there are transaction types that are neither purchases nor sales.

Revised Query to Meet Requirements #

Here's a corrected version that addresses these issues, plus improves maintainability:

WITH PurchaseTypes AS (
    SELECT type FROM (VALUES 
        ('PUR'), ('PURF'), ('ISPUR'), ('BONPUR'), ('CONPUR'), 
        ('PMALL'), ('INJVSP'), ('CUSTTO'), ('DPURF'), ('ISDPUR'), ('MFALL')
    ) AS t(type)
),
SaleTypes AS (
    SELECT type FROM (VALUES 
        ('SAL'), ('SALF') -- Add all your sale-specific transaction types here
    ) AS t(type)
)
SELECT 
    value_date AS Date,
    SUM(CASE WHEN dt.tran_type IN (SELECT type FROM PurchaseTypes) THEN dt.nett_val ELSE 0 END)/10000000 AS Total_Purchase,
    SUM(CASE WHEN dt.tran_type IN (SELECT type FROM SaleTypes) THEN dt.nett_val ELSE 0 END)/10000000 AS Total_Sale,
    (SUM(CASE WHEN dt.tran_type IN (SELECT type FROM PurchaseTypes) THEN dt.nett_val ELSE 0 END) - 
     SUM(CASE WHEN dt.tran_type IN (SELECT type FROM SaleTypes) THEN dt.nett_val ELSE 0 END))/10000000 AS Net_Purchase,
    -- Keep Volume only if you need it; adjust logic if needed
    SUM(CASE WHEN dt.tran_type NOT IN ('DPURF') THEN dt.nett_val ELSE -1*dt.nett_val END)/10000000 AS Volume
FROM Deal dt
WHERE dt.rectype = 'L' 
  AND dt.value_date BETWEEN '01 Jul 2017' AND '31 Jul 2017' 
  AND dt.asset_type IN ('EQU','ETF','PRF','CCD','AT1')
GROUP BY dt.value_date
ORDER BY dt.value_date;

Key Improvements & Optimizations #

1. Explicit Transaction Type Groups: Using CTEs (PurchaseTypes and SaleTypes) lets you define your transaction types in one place, making the query easier to update later if new purchase/sale types are added. No more searching through multiple CASE statements to make changes.
2. Accurate Net Calculation: Net purchase is now explicitly calculated as Total_Purchase - Total_Sale, which excludes any non-purchase/sale transactions from affecting the result.
3. Grouped by Date: The GROUP BY value_date clause ensures you get a row per date, matching your desired output format.
4. Readability: Reusing the purchase/sale sums for the net calculation makes the logic clearer than repeating the entire CASE statement.
5. Indexing Suggestion: If your Deal table is large, adding a covering index can speed up this query significantly. Example:

   CREATE INDEX idx_deal_reporting ON Deal (rectype, value_date, asset_type) INCLUDE (tran_type, nett_val);
   

   This index covers all columns used in the WHERE, GROUP BY, and aggregation logic, avoiding full table scans.
6. Avoid Redundant Logic: If you don't need the Volume column (it wasn't mentioned in your core requirement), removing it will simplify the query further.

Final Checks #

• Make sure all your sale-specific transaction types are added to the SaleTypes CTE (I only included SAL and SALF as examples).
• Verify that nett_val is stored in the unit requiring division by 10,000,000 (e.g., if it's in tenths of a cent, this conversion makes sense).
• If tran_type can be NULL, those rows will be excluded from both purchase and sale totals, which is likely correct since they don't belong to either category.

Content of the question originates from Stack Exchange, asked by Deep Dh.