Question

我有这么长时间的问询,应该向我提供一些有关运输的信息,而且它运作良好,但表现得非常糟糕。装载量大约为4 500米。

SELECT
    DATE(paid_at) AS day,
    COUNT(*) as order_count,
    (
      SELECT COUNT(*) FROM line_items
      WHERE order_id IN (SELECT id from orders WHERE DATE(paid_at) = day)
    ) as product_count,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  colissimo 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
    ) as orders_co,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  colissimo 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
      AND paid_amount < 70
    ) as co_less_70,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  colissimo 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
      AND paid_amount >= 70
    ) as co_plus_70,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  mondial_relais 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
    ) as orders_mr,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  mondial_relais 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
      AND paid_amount < 70
    ) as mr_less_70,
    (
      SELECT COUNT(*) FROM orders
      WHERE shipping_method =  mondial_relais 
      AND DATE(paid_at) = day
      AND state IN ( paid , shipped , completed )
      AND paid_amount >= 70
    ) as mr_plus_70
    FROM orders
    WHERE MONTH(paid_at) = 11
    AND YEAR(paid_at) = 2011
    AND state IN ( paid , shipped , completed )
    GROUP BY day;

任何想法,我会做什么错做,或我能做些什么? 我有其他类似时间的询问,没有这么多时间来装载。我认为,这样做的速度将快于例如,每天都有个人询问(在我的方案规划中,而不是在结构上)。

Answer 1

这是因为你正在使用不需要的分局。

作为一项一般规则,如果你在主要选任合同条款内有一个分局,则分局将质疑选任主选任条款中各行各一份的表格,这样,如果你有7个分局,并且选择30天的时间范围,你将有效地管理210个分局(加上你的主要询问)。

(某些情况下,有些问题选择异构体会将分类排入主要问题,但作为一项一般规则,你可能不依赖此。)

在这种情况下,你不需要<条码><>条码/代码>的子序列,因为所有<条码>条码/代码>。您要求的数据载于主要询问中,因此,你可以将数据改成:

SELECT
    DATE(paid_at) AS day,
    COUNT(*) as order_count,
    (
      SELECT COUNT(*) FROM line_items
      WHERE order_id IN (SELECT id from orders WHERE DATE(paid_at) = day)
    ) as product_count,
    sum(case when shipping_method =  colissimo  then 1 end) as orders_co,
    sum(case when shipping_method =  colissimo  AND 
                  paid_amount < 70 then 1 end) as co_less_70,
    sum(case when shipping_method =  colissimo  AND 
                  paid_amount >= 70 then 1 end) as co_plus_70,
    sum(case when shipping_method =  mondial_relais  then 1 end) as orders_mr,
    sum(case when shipping_method =  mondial_relais  AND 
                  paid_amount < 70 then 1 end) as mr_less_70,
    sum(case when shipping_method =  mondial_relais  AND 
                  paid_amount >= 70 then 1 end) as mr_plus_70
    FROM orders
    WHERE MONTH(paid_at) = 11
    AND YEAR(paid_at) = 2011
    AND state IN ( paid , shipped , completed )
    GROUP BY day;

Answer 2

你的问询中的问题是,这 table了同一桌子。ORDER表的所有扫描(如果是的话)可改为多个SUM+CASE或COUNT+CASE,如。查询和案件说明。

友情链接