时间戳

首页 > 解决方案 > 带有过滤器的复杂表连接并使用“like”作为列

sql-server - 带有过滤器的复杂表连接并使用“like”作为列

问题描述

我有一个复杂的表格，显示销售信息的实际和预测，有多个上传日期。我希望能够在产品 ID 上加入表格，同时查找每个产品的百分比错误并且仅使用最新信息。这些产品来自众多供应商，因此它们都有不同的名称，但与同一产品相关。例如，来自两个供应商的产品 A 可能是名称供应商 1_A 和供应商 2_A，但我希望能够将它们添加到一列中以查找错误。以下是数据示例：

Type     | ID                | DateUpload     |Jan. | Feb. | March
Forecast   Supplier1_Apples       2018-01-01    5      6      5
Actual     Supplier1_Apples       2018-01-01    4      4      5
Forecast   Supplier1_Apples       2018-02-01    5      6      5
Actual     Supplier1_Apples       2018-02-01    4      6      5
Forecast   Supplier2_Apples       2018-02-01    6      6      5
Actual     Supplier2_Apples       2018-02-01    4      4      3
Forecast   Supplier3_Apples       2018-02-01    8      9      5
Actual     Supplier3_Apples       2018-02-01    7      8      5

我最终想要的桌子看起来像这样

ID    | DateUpload  | error_jan...
Apples   2018-02-01   (abs(total_actual - total_forecast))/total_actual

这个连接是否可以合并到一个包含所有过滤器的表中，或者我是否需要创建一个不同的表来为每个供应商获取错误，然后找到它的平均值？目前，我可以根据 ID 加入表以查找每个 ID 处的错误，但如果它们相似，则无法将它们全部组合起来以在此示例中查找 Apple 的整体错误。

标签： sql-server

解决方案

  • 您需要拆分 ID 以从 ID 中删除供应商
  • 然后使用 CTE 或子查询过滤掉 Actual 和 forcast 数据
  • 然后在 Id 和 DateUploaded 上加入两个 CTE
  • 然后按 Id 和 DateUploaded 分组
  • 现在您知道如何计算 Error 列的值了，我为 JAN 做了，希望它有效。

数据准备

declare @mytable as table(
Type varchar(10),
ID varchar(50),
DateUploaded datetime,
JAN decimal
)

insert into @mytable values ('Forecast', 'Supplier1_Apples','2018-01-01',5)
insert into @mytable values ('Actual', 'Supplier1_Apples','2018-01-01',4)

insert into @mytable values ('Forecast', 'Supplier1_Apples','2018-02-01',5)
insert into @mytable values ('Actual', 'Supplier1_Apples','2018-02-01',4)

insert into @mytable values ('Forecast', 'Supplier2_Apples','2018-02-01',6)
insert into @mytable values ('Actual', 'Supplier2_Apples','2018-02-01',4)

insert into @mytable values ('Forecast', 'Supplier3_Apples','2018-02-01',8)
insert into @mytable values ('Actual', 'Supplier3_Apples','2018-02-01',7)

询问：

;With ForecastCTE AS
(
    Select 
    substring(ID, CHARINDEX('_',ID)+1, LEN(ID)) as Id, 
    DateUploaded,
    JAN
    From @mytable
    Where Type = 'Forecast'
),
ActualCTE AS
(
    Select 
    substring(ID, CHARINDEX('_',ID)+1, LEN(ID)) as Id, 
    DateUploaded,
    JAN
    From @mytable
    Where Type = 'Actual'
)
Select 
    F.Id,
    F.DateUploaded,
    (abs((SUM(A.JAN) - SUM(F.JAN))/SUM(A.JAN))) AS error_jan
from ForecastCTE F
INNER join ActualCTE A on F.ID = A.ID and F.DateUploaded = A.DateUploaded
Group by F.Id,F.DateUploaded

输出 ：

Id      DateUploaded                error_jan
Apples  2018-01-01 00:00:00.000     0.250000
Apples  2018-02-01 00:00:00.000     0.266666

推荐阅读