时间戳

首页 > 解决方案 > 如何根据两列的值与另一个表的内连接来计算记录的比率？

sql - 如何根据两列的值与另一个表的内连接来计算记录的比率？

问题描述

我正在为我的公司将（希望如果我做得好）使用的内部工具开发一个新的分析师功能。

为简单起见，假设我们有

CREATE TABLE Products (
    ProductID varchar,
    Description varchar,
   ....
);

和

CREATE TABLE Orders (
    ProductID varchar,
    Bought date,
    Returned date,
   ....
);

这些表看起来像这样：

产品

产品编号 描述
SPO00 运动用品1
SPO01 运动用品2
SPO02 运动用品3
EL00 电子产品1
ELE02 电子产品2

订单

产品编号 买 回来
EL00 2021-01-05 2021-01-07
SPO00 2021-01-01 空值
SPO00 2021-01-05 2021-01-08
SPO00 2021-01-08 空值
SPO01 2021-01-10 空值
SPO01 2021-01-15 空值
SPO02 2021-01-18 2021-01-20

我想向我们的数据库发出请求并检索特定产品的描述，以及最终退回的已购买产品的百分比。

我还想在查询中添加特定参数，例如仅选择年初的订单以及特定部门的产品。

所以，它看起来像这样：

描述 返回的比率
运动用品1 0.33
运动用品2 0.00
运动用品3 1.0

因此，products 表可能有电子和运动产品线，ProductID 分别为 ELE00-ELE05 和 SPO00-SPO03。

上表抓取所有 ProductID 带有 SPO 前缀的产品，并获取特定产品的购买和退货率。

我只能得到特定的产品，但每行的返回比例是相同的。我认为是因为它没有为每个不同的产品进行比率计算。我认为它会进行一次总体比率计算并针对每种产品进行显示。

这是我尝试过的查询。

SELECT DISTINCT Product.Description, (CAST((SELECT DISTINCT COUNT(*) FROM Orders WHERE(ProductID like 'SPO%' AND Returned > '2021-01-01') AS FLOAT)) / (CAST((SELECT DISTINCT COUNT(*) FROM Orders WHERE (ProductID like 'SPO%' AND Bought > '2021-01-01') AS FLOAT)) AS returnedRatio
FROM Product INNER JOIN
Orders ON Orders.ProductID = Product.ProductID

我想我可能需要做一个嵌套查询来获取每个产品的比率，然后获取描述？

所有帮助将不胜感激，因为我从未做过非常复杂的查询，所以我仍在学习。

标签： sqlentity-frameworkssms

解决方案

这对你有用吗？

我case在函数内部使用了一个表达式count()来计算返回产品的数量。

将* 1.0整数除法转换为十进制除法，而无需显式转换。

样本数据

CREATE TABLE Products (
    ProductID nvarchar(5),
    Description nvarchar(50)
);

insert into Products (ProductId, Description) values
('SPO00',   'Sports product 1'),
('SPO01',   'Sports product 2'),
('SPO02',   'Sports product 3'),
('ELE00',   'Electronics product 1'),
('ELE02',   'Electronics product 2');

CREATE TABLE Orders (
    ProductID nvarchar(5),
    Bought date,
    Returned date
);

insert into Orders (ProductID, Bought, Returned) values
('ELE00', '2021-01-05', '2021-01-07'),
('SPO00', '2021-01-01', NULL),
('SPO00', '2021-01-05', '2021-01-08'),
('SPO00', '2021-01-08', NULL),
('SPO01', '2021-01-10', NULL),
('SPO01', '2021-01-15', NULL),
('SPO02', '2021-01-18', '2021-01-20');

解决方案

select p.Description,
       count(case when o.Returned is not null then 1 end) as ReturnCount,
       count(1) TotalCount,
       count(case when o.Returned is not null then 1 end) * 1.0 / count(1) as ReturnRatio
from Products p
join Orders o
  on o.ProductID = p.ProductID
where p.ProductID like 'SPO%'
  and o.Bought >= '2021-01-01'
group by p.Description;

结果

Description       ReturnCount  TotalCount  ReturnRatio
----------------  -----------  ----------  --------------
Sports product 1  1            3           0.333333333333
Sports product 2  0            2           0
Sports product 3  1            1           1

小提琴以查看实际情况。

推荐阅读