将一个矩阵除以另一个矩阵

Question

我有一个每月支付给平台的数据库，该数据库已初始化orders.engagement = 'M'或已初始化orders.engagement = 'R'，(您可以在后面的问题中看到MySQL Fiddle)。目前，约74%的项目已转为按月付款。我想知道每月的利率是多少，因为在过去的几个月里我只得到45%。

然后，我想计算计算以下关系的SQL:

Number of initiations resulting in at least one payment in the following months
---- divided ----
Number of initiations

我想对每个月做这个比率。但在接下来的测试中，我认为当我按月执行GROUP BY时，我"在接下来的几个月里至少损失了一笔付款"。

它是这样做的，以返回在接下来的几个月内至少导致一次付款的初始化次数:

SELECT orders_1.date, COUNT(DISTINCT orders_1.`gxo_vads_subscription`) count_first_recurrent
FROM `orders` AS orders_1
INNER JOIN `orders` AS orders_2 ON orders_1.`gxo_vads_subscription` = orders_2.`gxo_vads_subscription`
# Donde la transacion a logrado
WHERE `orders_1`.`status` = 2
AND `orders_1`.`engagement` = 'R' 
AND orders_2.`engagement` = 'M'
GROUP BY YEAR(orders_1.date), MONTH(orders_1.date)

它返回:

...
25/5/2021, 1:10    2 651
7/6/2021, 1:10     2 443

这里是与数据库的提取相关的Fiddle

这是每个mez初始化的所有事务:

SELECT orders_3.date, COUNT(DISTINCT orders_3.`gxo_vads_subscription`) count_initialization
FROM `orders` AS orders_3
-- INNER JOIN `orders` AS orders_4 ON orders_3.`gxo_vads_subscription` = orders_4.`gxo_vads_subscription`
# Où la transaction a réussi
WHERE orders_3.`status` = 2
# qui soit après la campagne de pâques
AND orders_3.`engagement` = 'M' 
AND `orders_3`.`gxo_means` = 'CB'
GROUP BY YEAR(orders_3.date), MONTH(orders_3.date)

返回:

...
1/5/2021, 8:04    86
1/6/2021, 4:54    36

有一个问题:与平均值相比，count_initialization/count_first_recurrent划分到第二的结果似乎太高了:

...
1/4/2021, 1:10    664,25
11/5/2021, 1:10   662,75
7/6/2021, 1:10    610,75

这意味着导致至少一次付款的初始化次数是初始化次数的6倍，这是不可能的。

下面是获取0,74平均值的脚本:

SELECT (count_first_recurrent/count_initialization) FROM
(
(SELECT count(distinct `orders`.`gxo_vads_subscription`) count_first_recurrent
FROM `orders`
WHERE `orders`.`engagement` = 'R'
AND `orders`.`status` = 2 AND `orders`.`gxo_means` = 'CB') as a,

(SELECT count(distinct `orders`.`gxo_vads_subscription`) count_initialization
FROM `orders`
WHERE `orders`.`engagement` = 'M'
AND `orders`.`status` = 2 AND `orders`.`gxo_means` = 'CB') AS b
)

Answer 1

根据您的描述，一个更简单的整体查询是:

SELECT COUNT(DISTINCT CASE WHEN o.engagement = 'R' THEN o.gxo_vads_subscription END) as count_first_recurrent,
COUNT(DISTINCT CASE WHEN o.engagement = 'M' THEN o.gxo_vads_subscription END) as count_initialization
FROM `orders` o
WHERE o.status = 2 AND o.gxo_means = 'CB';

你可以做除法，但这是原始计数。

您可以轻松地按月展开:

SELECT YEAR(o.date), MONTH(o.date),
COUNT(DISTINCT CASE WHEN o.engagement = 'R' THEN o.gxo_vads_subscription END) as count_first_recurrent,
COUNT(DISTINCT CASE WHEN o.engagement = 'M' THEN o.gxo_vads_subscription END) as count_initialization
FROM `orders` o
WHERE o.status = 2 AND o.gxo_means = 'CB'
GROUP BY YEAR(o.date), MONTH(o.date);

这似乎回答了你的问题，但我不确定结果是否有用。所有的初始化都在同一个月内。