滞后 SQL 命令的替代方案

Question

我有一张桌子，里面有一张这样的桌子。

Month-----Book_Type-----sold_in_Dollars
Jan----------A------------ 100
Jan----------B------------ 120
Feb----------A------------ 50
Mar----------A------------ 60
Mar----------B------------ 30

依此类推，

我必须根据最近 2 个月的销售额计算每个月和书籍类型的预期销售额。 因此对于 3 月和类型 A 来说，它将是 (100+50)/2 = 75 对于 3 月和类型 B，该值为 120/1，因为没有 2 月的数据。

我试图使用滞后函数，但它不起作用，因为几行中缺少数据。

对此有什么想法吗？

Answer 1

由于它计划忽略缺失值，因此这应该可行。目前没有数据库来测试它，但会在早上再试一次

select 
  month, 
  book_type, 
  sold_in_dollars, 
  avg(sold_in_dollars) over (partition by book_type order by month
    range between interval '2' month preceding and interval '1' month preceding) as avg_sales
from myTable;

这种假设月份有一个日期数据类型并且可以排序......如果它只是一个文本字符串那么你会需要别的东西。

通常，您可以只使用前 2 行和前 1 行之间的行，但这将采用之前的两个数据点，如果缺少行，则不一定是前两个月的数据点。

你可以用滞后来解决这个问题，但会更复杂一些。

Answer 2

据我所知，您可以为 lag() 指定默认值：（

  SELECT Book_Type, 
         (lag(sold_in_Dollars, 1, 0) OVER(PARTITION BY Book_Type ORDER BY Month) + lag(sold_in_Dollars, 2, 0) OVER(PARTITION BY Book_Type ORDER BY Month))/2 AS expected_sales
    FROM your_table
GROUP BY Book_Type

假设 Month 列并不真正包含 JAN 或 FEB，而是真实的、可订购的日期。）

Answer 3

怎么样（请原谅 sql server 语法，但你明白了）：

Select Book_type, AVG(sold_in_dollars)
from MyTable
where Month in (Month(DATEADD('mm'-1,GETDATE)),Month(DATEADD('mm'-2,GETDATE)))
group by booktype

Answer 4

分区外连接可以帮助创建丢失的数据。创建一组月份，然后按月份将这些值连接到每一行，并为每种图书类型执行一次连接。我在此示例中创建了一月到四月：

with test_data as
(
  select to_date('01-JAN-2010', 'DD-MON-YYYY') month, 'A' book_type, 100 sold_in_dollars from dual union all
  select to_date('01-JAN-2010', 'DD-MON-YYYY') month, 'B' book_type, 120 sold_in_dollars from dual union all
  select to_date('01-FEB-2010', 'DD-MON-YYYY') month, 'A' book_type, 50 sold_in_dollars from dual union all
  select to_date('01-MAR-2010', 'DD-MON-YYYY') month, 'A' book_type, 60 sold_in_dollars from dual union all
  select to_date('01-MAR-2010', 'DD-MON-YYYY') month, 'B' book_type, 30 sold_in_dollars from dual
)
select book_type, month, sold_in_dollars
  ,case when denominator = 0 then 'N/A' else to_char(numerator / denominator) end expected_sales
from
(
  select test_data.book_type, all_months.month, sold_in_dollars
    ,count(sold_in_dollars) over
      (partition by book_type order by all_months.month rows between 2 preceding and 1 preceding) denominator
    ,sum(sold_in_dollars) over
      (partition by book_type order by all_months.month rows between 2 preceding and 1 preceding) numerator
  from 
    (
      select add_months(to_date('01-JAN-2010', 'DD-MON-YYYY'), level-1) month from dual connect by level <= 4
    ) all_months
    left outer join test_data partition by (test_data.book_type) on all_months.month = test_data.month 
)
order by book_type, month