如何优化使用多个子查询和聚合函数的SQL查询？

I have an SQL query as follows. It serves the purpose but it is very slow and a bit complicated as it have many aggregate functions and sub queries. I find it complicated and very slow.

我有一个SQL查询如下。它有用,但它很慢,有点复杂,因为它有许多聚合函数和子查询。我发现它很复杂而且很慢。

Here is the query:

这是查询:

SELECT
    dd.period,
    DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing))) AS startdate, 
    MAX(dso.date_clearing) AS lastdate, 
    ROUND(SUM(dso.DSO_actual_calc)/SUM(dso.amount_received_group_currency),1) AS dso,
    ROUND(SUM(dso.DSO_overdue_calc)/SUM(dso.amount_received_group_currency),1) AS dsooverdue,
   (SELECT
        ROUND(SUM(dso1.DSO_actual_calc)/SUM(dso1.amount_received_group_currency),1)
    FROM fact_dso_cleared_items as dso1
        INNER JOIN dim_date dd1
            ON dso1.date_clearing = dd1.the_date
    WHERE dso1.date_clearing BETWEEN
        DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing)))
             AND MAX(dso.date_clearing))) AS dso_rltm,
   (SELECT
        ROUND(SUM(dso2.DSO_overdue_calc)/SUM(dso2.amount_received_group_currency), 1)
    FROM fact_dso_cleared_items as dso2
        INNER JOIN dim_date as dd2
            ON dso2.date_clearing = dd2.the_date
    WHERE dso2.date_clearing BETWEEN
        DATEADD(day,1,DATEADD(month,-12,MAX(dso.date_clearing)))
        AND MAX(dso.date_clearing))) AS dso_overdue_rltm
FROM fact_dso_cleared_items AS dso
    INNER JOIN dim_date dd
        ON dso.date_clearing = dd.the_date
WHERE dd.period IN('2012/01','2012/02','2012/03','2012/04','2012/05','2012/06',
                   '2012/07','2012/08','2012/09','2012/10','2012/11','2012/12'))
GROUP BY dd.period
ORDER BY dd.period

Here is diagram for this query that shows tables and fields.

以下是此查询的图表,其中显示了表和字段。

And here are results of this query.

以下是此查询的结果。

What are the areas I should start improving in this query for simplification and performance?

为简化和性能,我应该在此查询中开始改进哪些方面?

1 个解决方案

#1

First attempt. I'm trying to kill the multiple aggregate functions, because you are using DATEADD(Day, 1, DATEADD(Month, -12, MAX(dso.date_clearing))) multiple times. I think a with query would help to.

第一次尝试。我试图杀死多个聚合函数,因为你多次使用DATEADD(Day,1,DATEADD(Month,-12,MAX(dso.date_clearing)))。我认为查询会有所帮助。

First attempt.

SELECT *

    ,dso_rltm           = (SELECT ROUND(SUM(dso1.DSO_actual_calc) / SUM(dso1.amount_received_group_currency), 1)
                           FROM fact_dso_cleared_items as dso1
                           INNER JOIN dim_date dd1 ON dso1.date_clearing = dd1.the_date
                           WHERE dso1.date_clearing BETWEEN data.startdate AND data.lastdate)

   ,dso_overdue_rltm    = (SELECT ROUND(SUM(dso2.DSO_overdue_calc) / SUM(dso2.amount_received_group_currency), 1)
                           FROM fact_dso_cleared_items as dso2
                           INNER JOIN dim_date as dd2 ON dso2.date_clearing = dd2.the_date 
                           WHERE dso2.date_clearing BETWEEN data.startdate AND data.lastdate)
FROM (
    SELECT

         period     = dd.period
        ,startdate  = DATEADD(Day, 1, DATEADD(Month, -12, MAX(dso.date_clearing))) 
        ,lastdate   = MAX(dso.date_clearing) 
        ,dso        = ROUND(SUM(dso.DSO_actual_calc) / SUM(dso.amount_received_group_currency), 1)
        ,dsooverdue = ROUND(SUM(dso.DSO_overdue_calc) / SUM(dso.amount_received_group_currency), 1)

    FROM fact_dso_cleared_items AS dso
    INNER JOIN dim_date dd ON dso.date_clearing = dd.the_date
    WHERE dd.period IN('2012/01','2012/02','2012/03','2012/04','2012/05','2012/06','2012/07','2012/08','2012/09','2012/10','2012/11','2012/12'))
    GROUP BY dd.period
    ) data
ORDER BY data.period