StarRocks报表开发中常用的SQL技巧

1、生成最近30天的日期

WITH date_series AS (
 SELECT
  CURRENT_DATE() - INTERVAL ( RN.n ) DAY AS date_value 
 FROM
  ( SELECT ROW_NUMBER() OVER () - 1 AS n FROM information_schema.COLUMNS LIMIT 30 ) RN 
)
SELECT 
*
FROM  date_series
ORDER BY date_value ;

2、获取每个工序的不良原因TOP3

 WITH reason_summary AS (
    SELECT
        processOperationName,  -- 工序编码
        reasonCode,            -- 不良原因编码
        reasonCodeDesc,        -- 不良原因名称
        SUM(ngQuantity) AS total_ng_quantity  -- 不良数量
    FROM
        ods_xp.ods_mes_nglotrecord
    WHERE  
        bizdate = '${bizdate}'
        AND reasonCode IS NOT NULL
    GROUP BY
        processOperationName,reasonCode, reasonCodeDesc
),
rn_reason_result AS (
    SELECT 
        processOperationName,
        reasonCode,
        reasonCodeDesc,
        total_ng_quantity,
        ROUND((total_ng_quantity / SUM(total_ng_quantity) OVER (PARTITION BY processOperationName)) * 100, 2) AS  ng_pct,
        ROW_NUMBER() OVER(PARTITION BY processOperationName ORDER BY total_ng_quantity DESC)  rn 
    FROM  reason_summary
)
 SELECT 
    processOperationName,
    reasonCode   reason_code,
    reasonCodeDesc   reason_code_desc,
    total_ng_quantity,
    ng_pct,
    '${bizdate}'   bizdate,
     NOW()       refresh_time 
FROM  rn_reason_result 
WHERE  rn <= 3 ;

(1)、通过ROW_NUMBER()窗口函数对工序进行分区，并按照不良数量进行降序排列；然后再过滤每个分组小于等于3的数据。

(2)、计算一个工序中某个原因的不良数量占所有原因的不良数量的百分比，SUM(total_ng_quantity) OVER (PARTITION BY processOperationName) 可以统计一个工序中所有原因的不良数量的总和。

        如果不区分工序，SUM(total_ng_quantity) OVER () 。

 

3、有一张产品订单表，ods_xp.ods_mes_productorder ，表中有productOrderName 订单名称，productSpecName 产品名称, planReleasedTime计划开工时间，planFinishedTime计划完工时间，createQuantity 计划产量。 例如订单日期开工日期为2025-05-01，完工日期为2025-06-30， 如何计算产品在2025-05-15到2025-06-15的产量？

按生产日期区间重叠计算，假设产量在生产周期内均匀分布：

SELECT 
    productSpecName AS 产品名称,
    SUM(createQuantity * 
        DATEDIFF(
            LEAST(planFinishedTime, '2025-06-15'),
            GREATEST(planReleasedTime, '2025-05-15')
        ) / 
        DATEDIFF(planFinishedTime, planReleasedTime)
    ) AS 区间产量
FROM 
    ods_xp.ods_mes_productorder
WHERE 
    planReleasedTime <= '2025-06-15' 
    AND planFinishedTime >= '2025-05-15'
GROUP BY 
    productSpecName;

4、