给定年份的 MySQL 单位/订单分布

Question

我正在寻找一个 MySQL 查询(子查询很好)，它将以下列格式获取过去一年中每个订单的单位分布:

units_per_order     |     number_of_orders     |     percent_of_total---------------------------------------------------------------------1                   |                 7500     |                  55%2 3 456-1010-2030-5050–100100+

编辑:

要查询的表和必要的列:

Table: ordersFields: orders_id, date_purchasedTable: orders_productsFields: orders_id, products_quantity

这是我现在拥有的:

SELECT COUNT(*) AS cnt, qty FROM (        SELECT SUM(products_quantity) as qty, orders_id        FROM orders_products op LEFT JOIN orders o USING (orders_id)        WHERE date_purchased LIKE '2012%'        GROUP BY orders_id    ) AS new_table WHERE qty != 0 GROUP BY qty ORDER BY qty

有什么想法吗？

Answer 1

好的，这就是我最终得到的。如果您发现任何提高效率的方法，请提出建议。

SELECT qty, cnt, ROUND(((cnt / total) * 100)) AS percent FROM (
    SELECT qty, COUNT(*) AS cnt, total
    FROM (
        SELECT
            (
                CASE WHEN (SUM(products_quantity) < 6) THEN SUM(products_quantity)
                    WHEN (SUM(products_quantity) BETWEEN 6 AND 10) THEN 6
                    WHEN (SUM(products_quantity) BETWEEN 11 AND 20) THEN 11
                    WHEN (SUM(products_quantity) BETWEEN 21 AND 50) THEN 21
                    WHEN (SUM(products_quantity) BETWEEN 51 AND 100) THEN 51
                    ELSE 101
                END
            ) AS qty,
            orders_id,
            (
                SELECT COUNT(DISTINCT(orders_id))
                FROM orders JOIN orders_products USING (orders_id)
                WHERE date_purchased LIKE '2012%' AND products_quantity != 0
            ) AS total
        FROM orders_products op JOIN orders o USING (orders_id)
        WHERE date_purchased LIKE '2012%'
        GROUP BY orders_id
    ) AS new_table
    WHERE qty != 0 GROUP BY qty
) AS newer_table