代码之家 › 专栏 › 技术社区 › That guy

Microsoft SQL Server:获取上次购买日期的最高成本

sql-server sql

That guy · 技术社区 · 6 年前

我用SQL已经有一段时间了,所以有点生疏。假设你想把上个月和这个月购买的东西的成本进行比较。一个例子就是这样的数据表…

10月购买的物品价值3美元,但9月相同的物品成本为2美元和1美元。所以你可以得到最大日期的最大成本(那就是2美元而不是1美元)。每行数据都会发生这种情况。

我用一个存储的标量值函数来完成这项工作,但是在处理100k+行的数据时,速度不是很快。如何处理select查询本身?我以前做的是在select语句中同时选择max,只返回1,然后在select语句中调用该函数。出于速度方面的考虑,我希望在不使用存储过程或函数的情况下执行相同的操作。我知道下面的查询不起作用,因为您只能返回1个值,但这是我要做的。

选择
采购,项目,美元,
(从表中选择max(采购),max(美元)
式中:项目=845,月(采购)=1月(采购)=1上次成本
从表
< /代码> 

它应该显示的示例可以这样描述。




最好的方法是什么?



10月购买的物品价值3美元,但9月相同的物品成本为2美元和1美元。所以你可以得到最大日期的最大成本(那就是2美元而不是1美元)。这会发生在每一行数据上。

我用一个存储的标量值函数来完成这项工作,但是在处理100k+行的数据时,速度不是很快。如何处理select查询本身?我以前做的是在select语句中同时选择max,只返回1,然后在select语句中调用该函数。出于速度方面的考虑,我希望在不使用存储过程或函数的情况下执行相同的操作。我知道下面的查询不起作用,因为您只能返回1个值,但这是我要做的事情。

Select 
    Purchase, Item, USD, 
    (select MAX(Purchase), MAX(USD) from Table 
     where Item = 845 and MONTH(Purchase) = MONTH(Purchase) -1) LastCost 
from Table


它应该显示的示例可以这样描述。



最好的方法是什么?

5 回复 | 直到 6 年前

melpomene 6 年前

SELECT Date, item, usd, 
  LAG(Date, 1) OVER(Order by date asc) as FormerDate,
  LAG(usd, 1) OVER(Order by date asc) as FormerUsd 
from (select date, item, max(usd) as usd from Data group by date, item) t

这基本上返回当前条目前一天的最高价格。

Wolfgang Kais 6 年前

注意: Select MAX(Purchase), MAX(USD) from Table 不会返回最高日期的最高成本,但会返回最高日期和最高成本(无论日期如何)。

这是我将如何做到的(至少在SQL Server 2012上):

为了每个月只获得一条记录和一个项目(在最近的日期成本最高),我使用一个编号为采购日期和成本(每个项目和月份),按降序排序,先按日期,然后按成本。在下一步中,我只过滤编号为1(每个项目和月份的最大日期和月份的最大成本)的记录,并使用滞后函数访问以前的成本:

WITH 
  numbering (Purchase, Item, Cost, p_no) AS (
    SELECT Purchase,Item, Cost
      ,ROW_NUMBER() OVER (PARTITION BY Item, EOMONTH(Purchase) ORDER BY Purchase DESC, Cost DESC)
    FROM tbl
  )
SELECT Purchase, Item, Cost
  , LAG(Cost) OVER (PARTITION BY Item ORDER BY Purchase) AS LastCost
FROM numbering
WHERE p_no = 1

Zaynul Abadin Tuhin 6 年前

对于SQL Server 2017,下面的查询适用于示例数据

select purchase,item,
substring(usd,CHARINDEX(',',usd),len(usd)) as USD,
substring(usd,1,CHARINDEX(',',usd)) as lastcost from
(select max(purchase) as purchase,item, STRING_AGG (usd, ',') AS usd
 from
(
 select  purchase,item,max(usd) as usd from t
group by purchase,item

) as T group by item
) T1

iSR5 6 年前

对于您的结果,您需要使用 MAX() 和 ROW_NUMBER() 具有 OVER() . 然后按项目、年份和月份划分记录。这将确保在每个项目上,每年,每个月进行分类。这个 行数() 将作为将最后一条记录放在结果顶部的简单方法,因此您将为每个项目调用第1行以获取最新成本。在这之后,您可以使用它作为子查询,根据需要对其进行优化。首先(你的样品),你需要使用 CASE 为了分割美元(上一个和上一个成本)。然后从那里开始剩下的工作(简单的方法)。

我需要注意的是,先按年,然后按月对记录进行排序是很重要的。然后,如果你需要包括这一天,包括它。这样您就可以确保记录的排序正确。

因此,查询看起来像这样:

SELECT 
    MAX(Purchase) Purchase
,   MAX(Item) Item
,   MAX(CASE WHEN LastCost > USD THEN LastCost ELSE NULL END) USD
,   MAX(CASE WHEN LastCost = USD THEN LastCost ELSE NULL END) LastCost 
FROM (
SELECT 
    Purchase
,   Item
,   USD
,   MAX(USD) OVER(PARTITION BY Item, YEAR(Purchase), MONTH(Purchase)) LastCost
,   ROW_NUMBER() OVER(PARTITION BY Item, YEAR(Purchase), MONTH(Purchase) ORDER BY MONTH(Purchase)) RN
FROM Table
) D     
WHERE 
    RN = 1

shawnt00 6 年前

with data as (
    select Item, eomonth(Purchase) as PurchaseMonth, max(USD) as MaxUSD
    from T
    group by Item, eomonth(Purchase)
  )
select
    PurchaseMonth, Item,
    lag(MaxUSD) over (partition by Item order by PurchaseMonth) as PriorUSD
from data;