当前位置： 文江博客话题详情

MySQL GROUP BY 和限制连接

发布于 2024-12-25 06:19:36 字数 3430 浏览 2 评论 0原文

我尝试对以下三个表执行查询：

orders

id

created_at

vouchers

id

order_id

initial_value

current_value

vouchers_used_in_orders

voucher_id

order_id

amount_used

value_before

value_after

在订单中购买优惠券，id然后，该订单的信息会保存到凭证中，以记录购买时间。

稍后，该优惠券可用于购买另一个订单中的商品。每张优惠券都有现金价值，因此可以在多个订单中使用，直至其价值完全用完。

我正在尝试提出一个查询，该查询将按购买的年份和月份对优惠券购买进行分组，并显示购买了多少张优惠券以及首次使用它们之前经过了多长时间，最多十二个月。

这就是我到目前为止所拥有的：

SELECT
YEAR(o.created_at) AS year_purchased,
MONTH(o.created_at) AS month_purchased,
SUM(IF(v.current_value = v.initial_value, 1, 1)) AS number_sold,
SUM(IF(v.current_value = v.initial_value, 0, 1)) AS number_used,
SUM(IF(v.current_value = v.initial_value, 1, 0)) AS number_not_used,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at) AND MONTH(vuo.created_at) = (MONTH(o.created_at)), 1, 0)) AS number_used_month_0,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 1 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 1 MONTH)), 1, 0)) AS number_used_month_1,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 2 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 2 MONTH)), 1, 0)) AS number_used_month_2,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 3 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 3 MONTH)), 1, 0)) AS number_used_month_3,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 4 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 4 MONTH)), 1, 0)) AS number_used_month_4,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 5 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 5 MONTH)), 1, 0)) AS number_used_month_5,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 6 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 6 MONTH)), 1, 0)) AS number_used_month_6,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 7 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 7 MONTH)), 1, 0)) AS number_used_month_7,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 8 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 8 MONTH)), 1, 0)) AS number_used_month_8,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 9 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 9 MONTH)), 1, 0)) AS number_used_month_9,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 10 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 10 MONTH)), 1, 0)) AS number_used_month_10,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 11 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 11 MONTH)), 1, 0)) AS number_used_month_11,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 12 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 12 MONTH)), 1, 0)) AS number_used_month_12
FROM vouchers v
INNER JOIN orders o
ON o.id = v.order_id
LEFT OUTER JOIN vouchers_used_in_orders vu
ON vu.voucher_id = v.id
LEFT OUTER JOIN orders vuo
ON vuo.id = vu.id
GROUP BY
YEAR(o.created_at), MONTH(o.created_at)
ORDER BY
YEAR(o.created_at), MONTH(o.created_at)

如果购买的优惠券尚未使用，则会正确计算它们，但是一旦开始使用它们，外部联接就会导致优惠券被多次计算。此外，当我只想第一次使用时，它会计算优惠券的每次使用。

有人可以帮忙吗？

任何建议表示赞赏。

谢谢。

原文

I have the following three tables which I am trying to perform a query on:

orders

id

created_at

vouchers

id

order_id

initial_value

current_value

vouchers_used_in_orders

voucher_id

order_id

amount_used

value_before

value_after

A voucher is purchased in an order, the id of this order is then saved to the voucher to keep a record of when it was purchased.

At a later date, the voucher can then be used to purchase goods in another order. Each voucher has a cash value, so can be used in multiple orders until their value is fully used up.

I am trying to come up with a query which will group voucher purchases by the year and month they were purchased and show how many were purhcased and how much time passed before they were first used up to a maximum of twelve months.

This is what I have so far:

SELECT
YEAR(o.created_at) AS year_purchased,
MONTH(o.created_at) AS month_purchased,
SUM(IF(v.current_value = v.initial_value, 1, 1)) AS number_sold,
SUM(IF(v.current_value = v.initial_value, 0, 1)) AS number_used,
SUM(IF(v.current_value = v.initial_value, 1, 0)) AS number_not_used,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at) AND MONTH(vuo.created_at) = (MONTH(o.created_at)), 1, 0)) AS number_used_month_0,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 1 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 1 MONTH)), 1, 0)) AS number_used_month_1,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 2 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 2 MONTH)), 1, 0)) AS number_used_month_2,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 3 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 3 MONTH)), 1, 0)) AS number_used_month_3,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 4 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 4 MONTH)), 1, 0)) AS number_used_month_4,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 5 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 5 MONTH)), 1, 0)) AS number_used_month_5,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 6 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 6 MONTH)), 1, 0)) AS number_used_month_6,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 7 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 7 MONTH)), 1, 0)) AS number_used_month_7,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 8 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 8 MONTH)), 1, 0)) AS number_used_month_8,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 9 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 9 MONTH)), 1, 0)) AS number_used_month_9,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 10 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 10 MONTH)), 1, 0)) AS number_used_month_10,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 11 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 11 MONTH)), 1, 0)) AS number_used_month_11,
SUM(IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 12 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 12 MONTH)), 1, 0)) AS number_used_month_12
FROM vouchers v
INNER JOIN orders o
ON o.id = v.order_id
LEFT OUTER JOIN vouchers_used_in_orders vu
ON vu.voucher_id = v.id
LEFT OUTER JOIN orders vuo
ON vuo.id = vu.id
GROUP BY
YEAR(o.created_at), MONTH(o.created_at)
ORDER BY
YEAR(o.created_at), MONTH(o.created_at)

This counts the purchased vouchers correctly if they have not been used however once they start to get used, the outer joins cause the vouchers to be counted multiple times. Also, it counts each use of a voucher, when I only want the first use.

Can anyone help?

Any advice appreciated.

Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

潦草背影 2025-01-01 06:19:36

您可以在优惠券 ID 上使用 COUNT DISTINCT 吗？ （您需要将 if 语句中的 false 条件设置为 null，这样它们就不是计数器）

SELECT
YEAR(o.created_at) AS year_purchased,
MONTH(o.created_at) AS month_purchased,
SUM(IF(v.current_value = v.initial_value, 1, 1)) AS number_sold,
SUM(IF(v.current_value = v.initial_value, 0, 1)) AS number_used,
SUM(IF(v.current_value = v.initial_value, 1, 0)) AS number_not_used,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at) AND MONTH(vuo.created_at) = (MONTH(o.created_at)), vuo.voucher_id, null)) AS number_used_month_0,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 1 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 1 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_1,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 2 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 2 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_2,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 3 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 3 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_3,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 4 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 4 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_4,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 5 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 5 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_5,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 6 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 6 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_6,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 7 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 7 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_7,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 8 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 8 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_8,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 9 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 9 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_9,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 10 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 10 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_10,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 11 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 11 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_11,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 12 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 12 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_12
FROM vouchers v
INNER JOIN orders o
ON o.id = v.order_id
LEFT OUTER JOIN vouchers_used_in_orders vu
ON vu.voucher_id = v.id
LEFT OUTER JOIN orders vuo
ON vuo.id = vu.id
GROUP BY
YEAR(o.created_at), MONTH(o.created_at)
ORDER BY
YEAR(o.created_at), MONTH(o.created_at)

Could you use a COUNT DISTINCT on the voucher id? (You will need to set the false condition in the if statement to be null so they are not counter)

SELECT
YEAR(o.created_at) AS year_purchased,
MONTH(o.created_at) AS month_purchased,
SUM(IF(v.current_value = v.initial_value, 1, 1)) AS number_sold,
SUM(IF(v.current_value = v.initial_value, 0, 1)) AS number_used,
SUM(IF(v.current_value = v.initial_value, 1, 0)) AS number_not_used,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at) AND MONTH(vuo.created_at) = (MONTH(o.created_at)), vuo.voucher_id, null)) AS number_used_month_0,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 1 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 1 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_1,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 2 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 2 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_2,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 3 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 3 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_3,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 4 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 4 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_4,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 5 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 5 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_5,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 6 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 6 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_6,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 7 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 7 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_7,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 8 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 8 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_8,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 9 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 9 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_9,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 10 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 10 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_10,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 11 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 11 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_11,
COUNT(DISTINCT IF(YEAR(vuo.created_at) = YEAR(o.created_at + INTERVAL 12 MONTH) AND MONTH(vuo.created_at) = (MONTH(o.created_at + INTERVAL 12 MONTH)), vuo.voucher_id, null)) )) AS number_used_month_12
FROM vouchers v
INNER JOIN orders o
ON o.id = v.order_id
LEFT OUTER JOIN vouchers_used_in_orders vu
ON vu.voucher_id = v.id
LEFT OUTER JOIN orders vuo
ON vuo.id = vu.id
GROUP BY
YEAR(o.created_at), MONTH(o.created_at)
ORDER BY
YEAR(o.created_at), MONTH(o.created_at)
回复 原文
人│生佛魔见 2025-01-01 06:19:36

这应该可以为您提供几乎所有您正在寻找的内容。除了每月的计数之外，我还添加了每个月的使用总量。我首先开始查询以获取优惠券、其初始日期以及每个您关心的 12 个月。我在每个相应的“每张优惠券”基础上预先计算一次月份，因此它可以帮助简化您的 SUM( IF() ) 聚合构造。为了检测凭证的单次计数，在我的预查询（V1）中，
我保留了与购买优惠券相关的原始订单。因此，通过使用它，我可以对同一订单 ID 进行 SUM( IF() )。如果是，则将其计入“已使用的优惠券”中。通过左连接到凭证使用表，如果返回为 null，则没有使用它的实例，并且将是 NULL 值。

您会注意到，我还采取了预防措施，通过明确防止用于购买凭证的订单 ID 被计数并错误地丢弃计数，来防止通过 LEFT JOIN 包含原始订单。

  LEFT JOIN Vouchers_Used_In_Orders VUIO
     ON V1.ID = VUIO.Voucher_ID
     AND NOT V1.OriginalOrderID = VUIO.Order_ID

我无法真正测试的唯一元素，并且现在没有 MySQL 是

SUM( IF( VUIO.Voucher_ID = null, 1, 0 )) as VouchersNotUsed，

我认为它会消失，但可能需要将其更改为 SUM( IF(VUIO.Voucher_ID 为 null, 1, 0))。希望这对您有帮助。

SELECT 
      YEAR( v1.VoucherStarted ) as VoucherYear,
      MONTH( v1.VoucherStarted ) as VoucherMonth,
      COUNT( distinct( v1.ID )) as UniqueVouchers,
      SUM( IF( v1.OriginalOrderID = O.ID, 1, 0 )) as VouchersUsed,
      SUM( IF( VUIO.Voucher_ID = null, 1, 0 )) as VouchersNotUsed,
      SUM( IF( v1.OriginalOrderID = O.ID, 0, 1 )) as TimesVouchersUsed,

      SUM( IF( O.Created_At >= v1.VoucherStarted AND O.Created_At < v1.Plus1Month ), 1, 0 ) as Month1Cnt,
      SUM( IF( O.Created_At >= v1.VoucherStarted AND O.Created_At < v1.Plus1Month ), VUIO.Amount_Used , 0 ) as Month1Amt,

      SUM( IF( O.Created_At >= v1.Plus1Month AND O.Created_At < v1.Plus2Month ), 1, 0 ) as Month2Cnt,
      SUM( IF( O.Created_At >= v1.Plus1Month AND O.Created_At < v1.Plus2Month ), VUIO.Amount_Used , 0 ) as Month2Amt,

      SUM( IF( O.Created_At >= v1.Plus2Month AND O.Created_At < v1.Plus3Month ), 1, 0 ) as Month3Cnt,
      SUM( IF( O.Created_At >= v1.Plus2Month AND O.Created_At < v1.Plus3Month ), VUIO.Amount_Used , 0 ) as Month3Amt,

      SUM( IF( O.Created_At >= v1.Plus3Month AND O.Created_At < v1.Plus4Month ), 1, 0 ) as Month3Cnt,
      SUM( IF( O.Created_At >= v1.Plus3Month AND O.Created_At < v1.Plus4Month ), VUIO.Amount_Used , 0 ) as Month3Amt,

      SUM( IF( O.Created_At >= v1.Plus4Month AND O.Created_At < v1.Plus5Month ), 1, 0 ) as Month4Cnt,
      SUM( IF( O.Created_At >= v1.Plus4Month AND O.Created_At < v1.Plus5Month ), VUIO.Amount_Used , 0 ) as Month4Amt,

      SUM( IF( O.Created_At >= v1.Plus5Month AND O.Created_At < v1.Plus6Month ), 1, 0 ) as Month5Cnt,
      SUM( IF( O.Created_At >= v1.Plus5Month AND O.Created_At < v1.Plus6Month ), VUIO.Amount_Used , 0 ) as Month5Amt,

      SUM( IF( O.Created_At >= v1.Plus6Month AND O.Created_At < v1.Plus7Month ), 1, 0 ) as Month6Cnt,
      SUM( IF( O.Created_At >= v1.Plus6Month AND O.Created_At < v1.Plus7Month ), VUIO.Amount_Used , 0 ) as Month6Amt,

      SUM( IF( O.Created_At >= v1.Plus7Month AND O.Created_At < v1.Plus8Month ), 1, 0 ) as Month7Cnt,
      SUM( IF( O.Created_At >= v1.Plus7Month AND O.Created_At < v1.Plus8Month ), VUIO.Amount_Used , 0 ) as Month7Amt,

      SUM( IF( O.Created_At >= v1.Plus8Month AND O.Created_At < v1.Plus9Month ), 1, 0 ) as Month8Cnt,
      SUM( IF( O.Created_At >= v1.Plus8Month AND O.Created_At < v1.Plus9Month ), VUIO.Amount_Used , 0 ) as Month8Amt,

      SUM( IF( O.Created_At >= v1.Plus9Month AND O.Created_At < v1.Plus10Month ), 1, 0 ) as Month9Cnt,
      SUM( IF( O.Created_At >= v1.Plus9Month AND O.Created_At < v1.Plus10Month ), VUIO.Amount_Used , 0 ) as Month9Amt,

      SUM( IF( O.Created_At >= v1.Plus10Month AND O.Created_At < v1.Plus11Month ), 1, 0 ) as Month10Cnt,
      SUM( IF( O.Created_At >= v1.Plus10Month AND O.Created_At < v1.Plus11Month ), VUIO.Amount_Used , 0 ) as Month10Amt,

      SUM( IF( O.Created_At >= v1.Plus11Month AND O.Created_At < v1.Plus12Month ), 1, 0 ) as Month11Cnt,
      SUM( IF( O.Created_At >= v1.Plus11Month AND O.Created_At < v1.Plus12Month ), VUIO.Amount_Used , 0 ) as Month11Amt,

      SUM( IF( O.Created_At >= v1.Plus12Month ), 1, 0 ) as Month12Cnt,
      SUM( IF( O.Created_At >= v1.Plus12Month ), VUIO.Amount_Used , 0 ) as Month12Amt

   from 
      ( SELECT
              v.id,
              v.initial_value,
              v.order_id as OriginalOrderID
              vo.created_at as VoucherStarted,
              date_add( vo.created_at, INTERVAL 1 MONTH ) as Plus1Month,
              date_add( vo.created_at, INTERVAL 2 MONTH ) as Plus2Month,
              date_add( vo.created_at, INTERVAL 3 MONTH ) as Plus3Month,
              date_add( vo.created_at, INTERVAL 4 MONTH ) as Plus4Month,
              date_add( vo.created_at, INTERVAL 5 MONTH ) as Plus5Month,
              date_add( vo.created_at, INTERVAL 6 MONTH ) as Plus6Month,
              date_add( vo.created_at, INTERVAL 7 MONTH ) as Plus7Month,
              date_add( vo.created_at, INTERVAL 8 MONTH ) as Plus8Month,
              date_add( vo.created_at, INTERVAL 9 MONTH ) as Plus9Month,
              date_add( vo.created_at, INTERVAL 10 MONTH ) as Plus10Month,
              date_add( vo.created_at, INTERVAL 12 MONTH ) as Plus11Month,
              date_add( vo.created_at, INTERVAL 12 MONTH ) as Plus12Month
           from
              vouchers v
                 join orders vo
                    ON v.order_id = vo.order_id ) V1

      LEFT JOIN Vouchers_Used_In_Orders VUIO
         ON V1.ID = VUIO.Voucher_ID
         AND NOT V1.OriginalOrderID = VUIO.Order_ID

         LEFT JOIN Orders O
            ON VUIO.Order_ID = O.ID
            AND O.Created_At between V1.VoucherStarted and V1.Plus12Month

   GROUP BY
      YEAR( v1.VoucherStarted ),
      MONTH( v1.VoucherStarted )

This should give you almost all you are looking for. In addition to the counts per month, I also added the total amount used per those months. I first started a query to get the voucher, its initial date and that plus 12 months per your concern. I am pre-computing the months once at each respective "per Voucher" basis so it can help simplify your SUM( IF() ) constructs for aggregations. For detecting single count of vouchers, in my prequery (V1),
I retained the original order associated with the purchase of the voucher. So by using that, I can do a SUM( IF() ) of that same order ID. If its the one, it gets counted in the VouchersUsed. With a left join to the voucher used table, if that comes back as null, then there are no instances of it being used and would be a NULL value.

You'll notice, I also took a precaution to prevent including the original order via the LEFT JOIN by explicitly preventing the order ID used to PURCHASE the voucher from getting counted and falsely throwing off a count.

  LEFT JOIN Vouchers_Used_In_Orders VUIO
     ON V1.ID = VUIO.Voucher_ID
     AND NOT V1.OriginalOrderID = VUIO.Order_ID

The only element I can't really test, and not having MySQL now is the

SUM( IF( VUIO.Voucher_ID = null, 1, 0 )) as VouchersNotUsed,

I think it will go, but may need to change it to SUM( IF( VUIO.Voucher_ID is null, 1, 0)). Hope this helps you out.

SELECT 
      YEAR( v1.VoucherStarted ) as VoucherYear,
      MONTH( v1.VoucherStarted ) as VoucherMonth,
      COUNT( distinct( v1.ID )) as UniqueVouchers,
      SUM( IF( v1.OriginalOrderID = O.ID, 1, 0 )) as VouchersUsed,
      SUM( IF( VUIO.Voucher_ID = null, 1, 0 )) as VouchersNotUsed,
      SUM( IF( v1.OriginalOrderID = O.ID, 0, 1 )) as TimesVouchersUsed,

      SUM( IF( O.Created_At >= v1.VoucherStarted AND O.Created_At < v1.Plus1Month ), 1, 0 ) as Month1Cnt,
      SUM( IF( O.Created_At >= v1.VoucherStarted AND O.Created_At < v1.Plus1Month ), VUIO.Amount_Used , 0 ) as Month1Amt,

      SUM( IF( O.Created_At >= v1.Plus1Month AND O.Created_At < v1.Plus2Month ), 1, 0 ) as Month2Cnt,
      SUM( IF( O.Created_At >= v1.Plus1Month AND O.Created_At < v1.Plus2Month ), VUIO.Amount_Used , 0 ) as Month2Amt,

      SUM( IF( O.Created_At >= v1.Plus2Month AND O.Created_At < v1.Plus3Month ), 1, 0 ) as Month3Cnt,
      SUM( IF( O.Created_At >= v1.Plus2Month AND O.Created_At < v1.Plus3Month ), VUIO.Amount_Used , 0 ) as Month3Amt,

      SUM( IF( O.Created_At >= v1.Plus3Month AND O.Created_At < v1.Plus4Month ), 1, 0 ) as Month3Cnt,
      SUM( IF( O.Created_At >= v1.Plus3Month AND O.Created_At < v1.Plus4Month ), VUIO.Amount_Used , 0 ) as Month3Amt,

      SUM( IF( O.Created_At >= v1.Plus4Month AND O.Created_At < v1.Plus5Month ), 1, 0 ) as Month4Cnt,
      SUM( IF( O.Created_At >= v1.Plus4Month AND O.Created_At < v1.Plus5Month ), VUIO.Amount_Used , 0 ) as Month4Amt,

      SUM( IF( O.Created_At >= v1.Plus5Month AND O.Created_At < v1.Plus6Month ), 1, 0 ) as Month5Cnt,
      SUM( IF( O.Created_At >= v1.Plus5Month AND O.Created_At < v1.Plus6Month ), VUIO.Amount_Used , 0 ) as Month5Amt,

      SUM( IF( O.Created_At >= v1.Plus6Month AND O.Created_At < v1.Plus7Month ), 1, 0 ) as Month6Cnt,
      SUM( IF( O.Created_At >= v1.Plus6Month AND O.Created_At < v1.Plus7Month ), VUIO.Amount_Used , 0 ) as Month6Amt,

      SUM( IF( O.Created_At >= v1.Plus7Month AND O.Created_At < v1.Plus8Month ), 1, 0 ) as Month7Cnt,
      SUM( IF( O.Created_At >= v1.Plus7Month AND O.Created_At < v1.Plus8Month ), VUIO.Amount_Used , 0 ) as Month7Amt,

      SUM( IF( O.Created_At >= v1.Plus8Month AND O.Created_At < v1.Plus9Month ), 1, 0 ) as Month8Cnt,
      SUM( IF( O.Created_At >= v1.Plus8Month AND O.Created_At < v1.Plus9Month ), VUIO.Amount_Used , 0 ) as Month8Amt,

      SUM( IF( O.Created_At >= v1.Plus9Month AND O.Created_At < v1.Plus10Month ), 1, 0 ) as Month9Cnt,
      SUM( IF( O.Created_At >= v1.Plus9Month AND O.Created_At < v1.Plus10Month ), VUIO.Amount_Used , 0 ) as Month9Amt,

      SUM( IF( O.Created_At >= v1.Plus10Month AND O.Created_At < v1.Plus11Month ), 1, 0 ) as Month10Cnt,
      SUM( IF( O.Created_At >= v1.Plus10Month AND O.Created_At < v1.Plus11Month ), VUIO.Amount_Used , 0 ) as Month10Amt,

      SUM( IF( O.Created_At >= v1.Plus11Month AND O.Created_At < v1.Plus12Month ), 1, 0 ) as Month11Cnt,
      SUM( IF( O.Created_At >= v1.Plus11Month AND O.Created_At < v1.Plus12Month ), VUIO.Amount_Used , 0 ) as Month11Amt,

      SUM( IF( O.Created_At >= v1.Plus12Month ), 1, 0 ) as Month12Cnt,
      SUM( IF( O.Created_At >= v1.Plus12Month ), VUIO.Amount_Used , 0 ) as Month12Amt

   from 
      ( SELECT
              v.id,
              v.initial_value,
              v.order_id as OriginalOrderID
              vo.created_at as VoucherStarted,
              date_add( vo.created_at, INTERVAL 1 MONTH ) as Plus1Month,
              date_add( vo.created_at, INTERVAL 2 MONTH ) as Plus2Month,
              date_add( vo.created_at, INTERVAL 3 MONTH ) as Plus3Month,
              date_add( vo.created_at, INTERVAL 4 MONTH ) as Plus4Month,
              date_add( vo.created_at, INTERVAL 5 MONTH ) as Plus5Month,
              date_add( vo.created_at, INTERVAL 6 MONTH ) as Plus6Month,
              date_add( vo.created_at, INTERVAL 7 MONTH ) as Plus7Month,
              date_add( vo.created_at, INTERVAL 8 MONTH ) as Plus8Month,
              date_add( vo.created_at, INTERVAL 9 MONTH ) as Plus9Month,
              date_add( vo.created_at, INTERVAL 10 MONTH ) as Plus10Month,
              date_add( vo.created_at, INTERVAL 12 MONTH ) as Plus11Month,
              date_add( vo.created_at, INTERVAL 12 MONTH ) as Plus12Month
           from
              vouchers v
                 join orders vo
                    ON v.order_id = vo.order_id ) V1

      LEFT JOIN Vouchers_Used_In_Orders VUIO
         ON V1.ID = VUIO.Voucher_ID
         AND NOT V1.OriginalOrderID = VUIO.Order_ID

         LEFT JOIN Orders O
            ON VUIO.Order_ID = O.ID
            AND O.Created_At between V1.VoucherStarted and V1.Plus12Month

   GROUP BY
      YEAR( v1.VoucherStarted ),
      MONTH( v1.VoucherStarted )
回复 原文
音盲 2025-01-01 06:19:36

我会将“优惠券”和“订单”合并为：

 voucher_orders:
     ID
     purchase_date
     value

然后将“优惠券使用”：

     VoucherID
     Amount
     use_date

要获取统计数据：

 select voucher_order.ID as v0, voucher_order.purchase_date, v2.ud left join
 (  select min( use_date ) ud from voucher_use as v1 where v1.VoucherID = v0.VoucherID limit 1 ) as v2 on v0.VoucherID = v2.VoucherID group by v0.purchase_date order by v0.purchase_date;

在代码中执行所有“剩余价值”内容。该信息不需要存储。

I'd combine 'vouchers' and 'orders' into:

 voucher_orders:
     ID
     purchase_date
     value

Then have 'voucher_use':

     VoucherID
     Amount
     use_date

To get stats:

 select voucher_order.ID as v0, voucher_order.purchase_date, v2.ud left join
 (  select min( use_date ) ud from voucher_use as v1 where v1.VoucherID = v0.VoucherID limit 1 ) as v2 on v0.VoucherID = v2.VoucherID group by v0.purchase_date order by v0.purchase_date;

Do all your 'value remaining' stuff in code. This info doesn't need to be stored.

回复 原文
~没有更多了~

关于作者

霊感

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

峩卟喜欢

文章 0 评论 0

一向肩并

文章 0 评论 0

潜伏

文章 0 评论 0

dongyinghao

文章 0 评论 0

百变从容

文章 0 评论 0

沧笙踏歌

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文