首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >关于SQL代码性能优化的建议

问关于SQL代码性能优化的建议
EN

Stack Overflow用户
提问于 2019-04-12 01:09:38
回答 1查看 65关注 0票数 1

我在Qubole中有一段代码,几乎需要3个小时才能执行。我正在寻找一些建议,以减少代码执行时间。

代码语言:javascript
复制
WITH
    -- Get latest date - 10 days before as day 
d
AS (
    SELECT CAST(CONCAT (
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 1, 4),
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 6, 2),
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 9, 2)
                ) AS BIGINT) AS day,
        CAST(CONCAT (
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 1, 4),
                '-',
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 6, 2),
                '-',
                SUBSTR(CAST(DATE_ADD('day', - 10, CAST(CURRENT_TIMESTAMP AS DATE)) AS VARCHAR), 9, 2)
                ) AS DATE) AS DATE,
        'FR' AS country
    )
SELECT 'Streaming' AS TRANSACTION,
    'Spotify' AS account,
    p_day,
    access,
    COUNT(DISTINCT customer_id) AS users,
    COUNT(*) AS units
FROM temp_1
WHERE day >= (
        SELECT day
        FROM d
        )
    AND country_code = (
        SELECT country
        FROM d
        )
GROUP BY 1,
    2,
    3,
    4

UNION ALL

SELECT 'Streaming' AS TRANSACTION,
    'Deezer' AS account,
    p_day,
    CASE 
        WHEN offer_code IN ('APP', 'BAO', 'BDP', 'BDS', 'BMO', 'BMS', 'BMW', 'BPF', 'BPP', 'BPR', 'BSO', 'BWE', 'BWP', 'BWS', 'DEE', 'DEP', 'ETT', 'EXT', 'FFX', 'IOS', 'OT1', 'PBH', 'PE1', 'PE2', 'PEM', 'PLS', 'PRM', 'PSC', 'PTP', 'SDP', 'SMG', 'SPF', 'SPP', 'SPR', 'SUP', 'SWE', 'SWP', '3M', 'FAM', 'GOO', 'GOF', 'HFP', 'HFF', 'HFI')
            THEN 'premium'
        WHEN offer_code IN ('BFR', 'MFS', 'MOD', 'SMR')
            THEN 'free'
        ELSE NULL
        END AS access,
    COUNT(DISTINCT masked_consumer_id) AS users,
    SUM(units_sold_streams) AS streams
FROM temp_2
WHERE day >= (
        SELECT day
        FROM d
        )
    AND country_code = (
        SELECT country
        FROM d
        )
GROUP BY 1,
    2,
    3,
    4

UNION ALL

SELECT 'Streaming' AS TRANSACTION,
    'Apple Music' AS account,
    ingest_datestamp AS p_day,
    'premium' AS access,
    COUNT(DISTINCT anonymized_person_id) AS users,
    COUNT(*) AS streams
FROM temp_streams1
WHERE ingest_datestamp >= (
        SELECT DATE
        FROM d
        )
    AND country_code = (
        SELECT country
        FROM d
        )
GROUP BY 1,
    2,
    3,
    4
EN

回答 1

Stack Overflow用户

发布于 2019-05-14 23:41:29

这对优化查询性能没有多大帮助,但有助于稍微简化代码。可以简化日期计算(在Presto上测试)

代码语言:javascript
复制
cast(DATE_FORMAT(DATE_ADD('day', -10, CURRENT_DATE),'%Y%m%d') as bigint) as day,
DATE_ADD('day', -10, CURRENT_DATE)                                       as date

为了提高性能,我建议根据日期和数据大小对表进行分区,同时根据国家代码,并将计算的日期作为参数传递,而不是作为子查询,以确保分区修剪工作。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55637874

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档