主页 / 问题 / 1590438
Accepted
dofi4ka
Asked:2024-08-13 06:06:22 +0000 UTC2024-08-13 06:06:22 +0000 UTC

SQL查询优化

  • 772

我在统一国家考试中发现了一项关于使用 sqlite3 处理数据库的有趣任务 问题情况

我不假思索地写了这个查询，它给出了正确的答案30%

SELECT SUM(products_moving.quantity * products_moving.cost) * 100 / (
    SELECT SUM(products_moving.quantity * products_moving.cost) FROM products_moving
    JOIN shops ON products_moving.shop_id = shops.id
    WHERE products_moving.op_type = 'Продажа'
        AND shops.region = 'Октябрьский'
) FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'
    AND products.type = 'Молоко'

但这远不是最完美的解决方案，因为理论上，我会在数据库中搜索两次十月镇地区的所有销售情况。我变得非常感兴趣，是否有可能以某种方式把它画得更漂亮、更优雅query？

我想到的第一件事是首先找到十月地区的所有销售额，将它们保存在某个地方，以卢布计算其金额，将该金额保存在某个地方，然后从之前保存的十月地区所有销售额的集合中，仅计数所有拥有“牛奶”类别的人，找到其金额（以卢布为单位）并计算百分比

那些。做类似这样的事情，但是在 SQL 中：

sales_in_region = filter(
    lambda row: row["op_type"] == 'Продажа' and row["region"] == 'Октябрьский',
    all_rows
)

sales_in_region_sum = sum(map(
    lambda row: row["quantity"] * row["cost"],
    sales_in_region
))

sales_in_region_milk = filter(
    lambda row: row["type"] == 'Молоко',
    sales_in_region
)

sales_in_region_milk_sum = sum(map(
    lambda row: row["quantity"] * row["cost"],
    sales_in_region_milk
))

print(sales_in_region_milk_sum // sales_in_region_sum * 100)

当然，在统一国家考试的框架内，优化这个查询是没有意义的，因为我不会多次这样做，即使需要十秒钟，我也不必担心，但我想知道是否有一种方法可以以某种方式完美地处理此类情况？

UPD：在下面的答案中，我们详细讨论了WITH AS运算符的想法，我在上面暗示过，但是从不同的角度看问题，我想出了一个不同的解决方案，该解决方案已经明显更快

SELECT 
    SUM(CASE WHEN products.type = 'Молоко' THEN products_moving.quantity * products_moving.cost END) 
    * 100 /  SUM(products_moving.quantity * products_moving.cost)
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'

这个想法是使用 CASE 运算符来过滤 SUM 中所需的行

python

2 个回答

  1. 考虑到评论中的所有要点，此查询将返回整数百分比，如果找不到合适的记录，则返回 null

    SELECT 
    SUM((products.type = 'Молоко') * products_moving.quantity * products_moving.cost) 
    * 100 / NULLIF(SUM(products_moving.quantity * products_moving.cost), 0)
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'
    • 2
  2. Best Answer

    是的，你可以这样做：

    WITH total_sales AS (
    SELECT SUM(products_moving.quantity * products_moving.cost) AS total
    FROM products_moving
    JOIN shops ON products_moving.shop_id = shops.id
    WHERE products_moving.op_type = 'Продажа'
        AND shops.region = 'Октябрьский'
)
SELECT SUM(products_moving.quantity * products_moving.cost) * 100 / total_sales.total
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
CROSS JOIN total_sales
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский'
    AND products.type = 'Молоко';

    WITH查询（公用表表达式）

    CASE 添加了使用inside 的 选项 SUM

    SELECT 
    SUM(CASE 
            WHEN products.type = 'Молоко' THEN products_moving.quantity * products_moving.cost 
            ELSE 0 
        END) * 100 / NULLIF(SUM(CASE 
                                    WHEN shops.region = 'Октябрьский' AND products_moving.op_type = 'Продажа' THEN products_moving.quantity * products_moving.cost 
                                    ELSE 0 
                                END), 0) AS milk_sales_percentage
FROM products_moving
JOIN products ON products_moving.article = products.article
JOIN shops ON products_moving.shop_id = shops.id
WHERE products_moving.op_type = 'Продажа'
    AND shops.region = 'Октябрьский';

    条件表达式CASE（条件聚合）

    • 1

相关问题