mysql - SQL 占先前总数的百分比

Question

我们有一个包含三列的 ORDERS 表:CUSTOMER_ID、ORDER_ID 和 PRODUCTS_ID。样本数据:

CUSTOMER_ID ORDER_ID    PRODUCT_ID  ORDER_DAY
C1          O1          P1          1-Jan-15
C1          O1          P2          1-Jan-15
C1          O1          P3          1-Jan-15

C2          O2          P6          2-Jan-15
C2          O2          P1          2-Jan-15
C2          O2          P3          2-Jan-15

C1          O3          P1          3-Jan-15
C1          O3          P3          3-Jan-15
C1          O3          P6          3-Jan-15
C1          O3          P7          3-Jan-15

你能编写一个查询来获得以下输出吗？

ORDER_ID    #PRODUCTS   #PRODUCTS_IN_PAST   %PRODUCTS_IN_PAST
O1          3           0                   0%
O2          3           0                   0%
O3          4           2                   50%

地点:

   #PRODUCTS: total number of products purchased in the order
   #PRODUCTS_IN_PAST is the number of the products in the particular order that were purchased by the same customer in the past.
   %PRODUCTS_IN_PAST = #PRODUCTS_IN_PAST/#PRODUCTS

Answer 1

select order_id, count(*) as numproducts, sum(is_previous), avg(is_previous)
from (select o.*,
             (case when o.product_id in (select o2.product_id
                                         from orders o2
                                         where o2.customer_id = o.customer_id and
                                               o2.order_date < o.order_date
                                         )
                    then 1 else 0
              end) as is_previous
      from orders o
     ) o
group by o.order_id;