根据每日库存全量表计算商户日均库存

Question

需求是这样的，一年中每天都会生成一个全量库存表goods，现在要计算goods中每个商品当日时点的日均库存（当年）、日均库存（当月）、日均库存（当季），并且有两种天数计算方式，一种是以当年（季、月）已过天数作为计算依据，一种是以当年（季、月）实际天数作为计算依据，求一个最优的解决方案。

Answer 1

引自微信小程序：皆我百晓生：

1. 确定计算日均库存的时间范围

首先需要明确计算日均库存的时间范围，根据需求描述，可以选择计算当年、当月或当季的日均库存。可以从每日库存全量表中选择特定时间范围的数据进行计算。

2. 获取每日库存数据

使用Oracle SQL语句查询每日库存全量表goods中特定时间范围内的库存数据。假设该表包含以下列：日期（date）、商品ID（goods_id）、库存量（stock）。

示例代码如下：

SELECT date, goods_id, stock
FROM goods
WHERE date >= start_date AND date <= end_date

其中，start_date和end_date是表示特定时间范围的起始日期和结束日期。

3. 根据计算依据获取对应天数

根据需求描述，需要计算两种天数计算方式的日均库存：以当年（季、月）已过天数作为计算依据和以当年（季、月）实际天数作为计算依据。

要获取对应的天数，可以使用Oracle SQL中的日期函数进行计算。如下所示：

• 以当年已过天数作为计算依据：

  SELECT TO_CHAR(date, 'YYYY') AS year,
       TO_CHAR(date, 'YYYY-MM') AS month,
       TO_CHAR(date, 'YYYY-Q') AS quarter,
       EXTRACT(DOY FROM date) AS day_of_year,
       EXTRACT(DAY FROM LAST_DAY(TRUNC(date, 'MONTH'))) AS days_in_month,
       EXTRACT(DAY FROM date) AS day_in_month
  FROM goods
  WHERE date >= start_date AND date <= end_date
  

  其中，date表示日期列，start_date和end_date为特定时间范围的起始日期和结束日期。

• 以当年实际天数作为计算依据：

  SELECT TO_CHAR(date, 'YYYY') AS year,
       TO_CHAR(date, 'YYYY-MM') AS month,
       TO_CHAR(date, 'YYYY-Q') AS quarter,
       EXTRACT(DAY FROM TO_DATE(TO_CHAR(date, 'YYYY-MM-DD'), 'YYYY-MM-DD')) AS actual_day
  FROM goods
  WHERE date >= start_date AND date <= end_date
  

  其中，date表示日期列，start_date和end_date为特定时间范围的起始日期和结束日期。

4. 计算日均库存

根据获取的每日库存数据和计算依据的天数，可以进行日均库存的计算。

• 以当年已过天数作为计算依据：

  SELECT year, SUM(stock) / MAX(day_of_year) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           EXTRACT(DOY FROM date) AS day_of_year,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year
  

• 以当年实际天数作为计算依据：

  SELECT year, SUM(stock) / MAX(actual_day) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           EXTRACT(DAY FROM TO_DATE(TO_CHAR(date, 'YYYY-MM-DD'), 'YYYY-MM-DD')) AS actual_day,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year
  

类似地，可以根据计算依据的天数，计算当月和当季的日均库存。

5. 计算日均库存（当月）

• 以当月已过天数作为计算依据：

  SELECT year, month, SUM(stock) / MAX(day_in_month) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           TO_CHAR(date, 'YYYY-MM') AS month,
           EXTRACT(DAY FROM date) AS day_in_month,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year, month
  

• 以当月实际天数作为计算依据：

  SELECT year, month, SUM(stock) / MAX(actual_day) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           TO_CHAR(date, 'YYYY-MM') AS month,
           EXTRACT(DAY FROM TO_DATE(TO_CHAR(date, 'YYYY-MM-DD'), 'YYYY-MM-DD')) AS actual_day,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year, month
  

6. 计算日均库存（当季）

• 以当季已过天数作为计算依据：

  SELECT year, quarter, SUM(stock) / MAX(day_in_quarter) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           TO_CHAR(date, 'YYYY-Q') AS quarter,
           EXTRACT(DOY FROM date) - EXTRACT(DOY FROM TRUNC(date, 'Q')) + 1 AS day_in_quarter,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year, quarter
  

• 以当季实际天数作为计算依据：

  SELECT year, quarter, SUM(stock) / MAX(actual_day) AS daily_stock_avg
  FROM (
    SELECT TO_CHAR(date, 'YYYY') AS year,
           TO_CHAR(date, 'YYYY-Q') AS quarter,
           EXTRACT(DAY FROM TO_DATE(TO_CHAR(date, 'YYYY-MM-DD'), 'YYYY-MM-DD')) AS actual_day,
           stock
    FROM goods
    WHERE date >= start_date AND date <= end_date
  )
  GROUP BY year, quarter
  

以上是一个基本的解决方案，你可以根据实际情况对查询语句进行优化和调整。希望对你有所帮助！