【LeetCode1384. 按年度列出销售总额】[MySQL with recursive/PostgreSQL with recursive/T-SQL with as/Oracle connect by]根据开始日期和结束日期，每个日期展开为一行

目录

• 题目地址
• 代码
  • • MySQL
    • PostgreSQL
    • Oracle
    • T-SQL
    • pandas

题目地址

https://leetcode.cn/problems/total-sales-amount-by-year/description/

代码

MySQL

WITH RECURSIVE DateSeries AS (
    SELECT product_id, period_start AS sale_date, period_end, average_daily_sales
    FROM Sales -- Assuming your table name is sales_data
    UNION ALL
    SELECT product_id, DATE_ADD(sale_date, INTERVAL 1 DAY), period_end, average_daily_sales
    FROM DateSeries
    WHERE sale_date < period_end
)

, YearlySales AS (
    SELECT 
        product_id,
        date_format(sale_date,"%Y") AS report_year,
        COUNT(DISTINCT sale_date) AS days_sold,
        SUM(average_daily_sales) AS total_amount
    FROM DateSeries
    GROUP BY product_id, YEAR(sale_date)
)

SELECT 
    y.product_id, 
    p.product_name, 
    y.report_year, 
    y.total_amount
FROM YearlySales y
JOIN Product p ON y.product_id = p.product_id -- Assuming there's a 'products' table with product_name
ORDER BY y.product_id, y.report_year;

PostgreSQL

WITH RECURSIVE DateSeries AS (
    SELECT 
        product_id, 
        period_start::timestamp AS sale_date, -- Explicitly cast period_start to timestamp
        period_end, 
        average_daily_sales
    FROM Sales -- Assuming your table name is sales_data
    UNION ALL
    SELECT 
        product_id, 
        sale_date + INTERVAL '1 DAY', 
        period_end, 
        average_daily_sales
    FROM DateSeries
    WHERE sale_date < period_end
)

, YearlySales AS (
    SELECT 
        product_id,
        to_char(sale_date, 'YYYY') AS report_year,
        COUNT(DISTINCT sale_date) AS days_sold,
        SUM(average_daily_sales) AS total_amount
    FROM DateSeries
    GROUP BY product_id, to_char(sale_date, 'YYYY')
)

SELECT 
    y.product_id, 
    p.product_name, 
    y.report_year, 
    y.total_amount
FROM YearlySales y
JOIN Product p ON y.product_id = p.product_id -- Assuming there's a 'products' table with product_name
ORDER BY y.product_id, y.report_year;

Oracle

WITH DateSeries (product_id, sale_date, period_end, average_daily_sales) AS (
    SELECT product_id, period_start + LEVEL - 1 AS sale_date, period_end, average_daily_sales
    FROM sales
    CONNECT BY LEVEL <= period_end - period_start + 1
    AND PRIOR product_id = product_id
    AND PRIOR SYS_GUID() IS NOT NULL
)
, YearlySales AS (
    SELECT 
        product_id,
        TO_CHAR(sale_date, 'YYYY') AS report_year,
        COUNT(DISTINCT sale_date) AS days_sold,
        SUM(average_daily_sales) AS total_amount
    FROM DateSeries
    GROUP BY product_id, TO_CHAR(sale_date, 'YYYY')
)

SELECT 
    y.product_id, 
    p.product_name, 
    y.report_year, 
    y.total_amount
FROM YearlySales y
JOIN Product p ON y.product_id = p.product_id -- Assuming there's a 'products' table with product_name
ORDER BY y.product_id, y.report_year;


-- SELECT * from DateSeries

T-SQL

T-SQL里好像没有with recursive关键字(有类似with recursive的功能，直接with as做就行，select的时候得在最后加OPTION (MAXRECURSION 0); )

WITH DateSeries AS (
    -- Anchor member definition
    SELECT product_id, period_start AS sale_date, period_end, average_daily_sales
    FROM Sales -- Assuming your table name is sales_data
    
    UNION ALL
    
    -- Recursive member definition
    SELECT ds.product_id, DATEADD(day, 1, ds.sale_date) AS sale_date, ds.period_end, ds.average_daily_sales
    FROM DateSeries ds
    WHERE ds.sale_date < ds.period_end
)

, YearlySales AS (
    SELECT 
        ds.product_id,
        YEAR(ds.sale_date) AS report_year,
        COUNT(DISTINCT ds.sale_date) AS days_sold,
        SUM(ds.average_daily_sales) AS total_amount
    FROM DateSeries ds
    GROUP BY ds.product_id, YEAR(ds.sale_date)
)

SELECT 
    ys.product_id, 
    p.product_name, 
    cast(ys.report_year as varchar) as report_year, 
    ys.total_amount
FROM YearlySales ys
JOIN Product p ON ys.product_id = p.product_id -- Replace with your actual table name if different
ORDER BY ys.product_id, ys.report_year
OPTION (MAXRECURSION 0); 

pandas

import pandas as pd

def total_sales(product: pd.DataFrame, sales: pd.DataFrame) -> pd.DataFrame:
    # Merge the product and sales dataframes
    merged_df = pd.merge(sales, product, on='product_id')

    # Create a date range for each sale period
    date_series = pd.concat([pd.DataFrame({
        'product_id': row['product_id'],
        'sale_date': pd.date_range(row['period_start'], row['period_end']),
        'average_daily_sales': row['average_daily_sales']
    }) for _, row in merged_df.iterrows()], ignore_index=True)

    # Calculate yearly sales
    yearly_sales = date_series.groupby(['product_id', date_series['sale_date'].dt.year]).agg(
        report_year=('sale_date', 'first'),
        days_sold=('sale_date', 'nunique'),
        total_amount=('average_daily_sales', 'sum')
    ).reset_index()

    # Merge with product dataframe to get product_name
    result_df = pd.merge(yearly_sales, product[['product_id', 'product_name']], on='product_id')

    # Rename columns to match the original query
    result_df = result_df.rename(columns={'report_year': 'report_year', 'total_amount': 'total_amount'})

    # Sort the dataframe
    result_df = result_df.sort_values(by=['product_id', 'report_year']).reset_index(drop=True)

    result_df['report_year'] = result_df['report_year'].dt.year.apply(str)
    return result_df[['product_id', 'product_name', 'report_year', 'total_amount']]