BI 中关于度量的SQL计算

最新推荐文章于 2023-12-05 11:03:15 发布

转载最新推荐文章于 2023-12-05 11:03:15 发布 · 197 阅读

本文深入探讨了SQL查询的执行过程，从逻辑层的变量设定与查询构建，到物理层的实际计算与度量值生成，再到使用分析函数进行更复杂的度量值计算。通过实例分析了如何利用WITH语句、子查询和聚合函数在不同层面上进行数据处理，以及在实际应用中如何选择合适的方法以提高查询效率。同时，介绍了如何将查询结果用于度量值的计算，包括计算总成本、总销售额、毛利润等关键指标，并通过不同计算方式（如使用WITH语句、聚合函数等）实现数据的多维度分析。

with 语句是执行一次并并存储在temp 表空间中。

1.逻辑层：计算度量值:

SET VARIABLE QUERY_SRC_CD='Report';
SELECT Times."Calendar Year" saw_0,
   "Sales Facts"."Amount Sold" saw_1,
   "Sales Facts"."Unit Cost" saw_2,
   "Sales Facts"."Gross Profit" saw_3
FROM SH ORDER BY saw_0 ;

WITH
SAWITH0 AS (select sum(T168.UNIT_COST) as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     T268.CALENDAR_YEAR as c3
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where (
T168.PROD_ID = T245.PROD_ID and
T168.TIME_ID = T245.TIME_ID and
T168.PROMO_ID = T245.PROMO_ID and
T168.CHANNEL_ID = T245.CHANNEL_ID and
T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR)
select distinct SAWITH0.c3 as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c1 as c3,
     SAWITH0.c2 - SAWITH0.c1 as c4
from
     SAWITH0
order by c1

2.物理层：计算度量值: 为什么不用with,没有用到子查询
SET VARIABLE QUERY_SRC_CD='Report';
SELECT Times."Calendar Year" saw_0,
"Sales Facts"."Amount Sold" saw_1,
"Sales Facts"."Unit Cost" saw_2,
"Sales Facts"."Gross Profit Physical" saw_3
FROM SH ORDER BY saw_0 ;

select T268.CALENDAR_YEAR as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     sum(T168.UNIT_COST) as c3,
     sum(T245.AMOUNT_SOLD - T168.UNIT_COST) as c4
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where (
T168.PROD_ID = T245.PROD_ID and
T168.TIME_ID = T245.TIME_ID and
T168.PROMO_ID = T245.PROMO_ID and
T168.CHANNEL_ID = T245.CHANNEL_ID and
T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR
order by c1

3.使用anayltics answer进行度量值的计算:

SET VARIABLE QUERY_SRC_CD='Report';

SELECT Times."Calendar Year" saw_0,
"Sales Facts"."Amount Sold" saw_1,
"Sales Facts"."Unit Cost" saw_2,
"Sales Facts"."Amount Sold"-"Sales Facts"."Unit Cost" saw_3
FROM SH ORDER BY saw_0 ;

WITH
SAWITH0 AS (select sum(T168.UNIT_COST) as c1,
     sum(T245.AMOUNT_SOLD) as c2,
     T268.CALENDAR_YEAR as c3
from
     SH.TIMES T268,
     SH.COSTS T168,
     SH.SALES T245
where ( T168.PROD_ID = T245.PROD_ID
     and T168.TIME_ID = T245.TIME_ID
     and T168.PROMO_ID = T245.PROMO_ID
     and T168.CHANNEL_ID = T245.CHANNEL_ID
     and T245.TIME_ID = T268.TIME_ID )
group by T268.CALENDAR_YEAR)
select distinct SAWITH0.c3
    as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c1 as c3,
     SAWITH0.c2 - SAWITH0.c1 as c4
from
     SAWITH0
order by c1

4.根据计算向导来计算的值
WITH
SAWITH0 AS (select sum(T245.AMOUNT_SOLD) as c1,
     T210.PROD_NAME as c2,
     T210.PROD_ID as c3,
     T210.PROD_CATEGORY as c5
from
     SH.TIMES T268,
     SH.PRODUCTS T210,
     SH.SALES T245
where ( T210.PROD_ID = T245.PROD_ID and T245.TIME_ID = T268.TIME_ID and T268.CALENDAR_YEAR = 2001 )
group by T210.PROD_ID, T210.PROD_NAME, T210.PROD_CATEGORY),
SAWITH1 AS (select sum(SAWITH0.c1) over (partition by SAWITH0.c3, SAWITH0.c5) as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c3 as c3,
     sum(SAWITH0.c1) over (partition by SAWITH0.c5) as c4,
     SAWITH0.c5 as c5
from
     SAWITH0),
SAWITH2 AS (select distinct SAWITH1.c5 as c1,
     SAWITH1.c2 as c2,
     SAWITH1.c1 as c3,
     case when SAWITH1.c4 = 0 then NULL else SAWITH1.c1 * 100.0 / nullif( SAWITH1.c4, 0) end as c4,
     SAWITH1.c3 as c5
from
     SAWITH1)
select SAWITH2.c1 as c1,
     SAWITH2.c2 as c2,
     SAWITH2.c3 as c3,
     SAWITH2.c4 as c4
from
     SAWITH2
order by c1, c2

==================================================================

==================================================================
1.Percent
Case
    when "SH"."Sales Facts"."Category Sales" = 0
   then NULL
    else 100.0 * ("SH"."Sales Facts"."Amount Sold"
/ "SH"."Sales Facts"."Category Sales")
end

2.Index
Case
    when "SH"."Sales Facts"."Category Sales" = 0
      then NULL
    else 1.0 * "SH"."Sales Facts"."Amount Sold" / "SH"."Sales Facts"."Category Sales"
end

3.PresentChange
Case
when ("SH"."Sales Facts"."Category Sales" is NULL or
        "SH"."Sales Facts"."Category Sales" = 0) and
       "SH"."Sales Facts"."Amount Sold" is not NULL and
       "SH"."Sales Facts"."Amount Sold" <> 0
         then 100.0
when ("SH"."Sales Facts"."Category Sales" is NULL or
        "SH"."Sales Facts"."Category Sales" = 0) and
       ("SH"."Sales Facts"."Amount Sold" is NULL or
        "SH"."Sales Facts"."Amount Sold" = 0)
         then 0.0
when "SH"."Sales Facts"."Amount Sold" IS NULL and
       "SH"."Sales Facts"."Category Sales" is not NULL and      "SH"."Sales Facts"."Category Sales" <> 0
         then -100.0
else 100.0 * ("SH"."Sales Facts"."Amount Sold" - "SH"."Sales Facts"."Category Sales") / "SH"."Sales Facts"."Category Sales"
end

4.Change
IfNull("SH"."Sales Facts"."Amount Sold", 0) -
IfNull("SH"."Sales Facts"."Category Sales", 0)