我在Qubole中有一段代码,几乎需要3个小时才能执行。我正在寻找一些建议,以减少代码执行时间。
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发布于 2019-05-14 23:41:29
这对优化查询性能没有多大帮助,但有助于稍微简化代码。可以简化日期计算(在Presto上测试)
cast(DATE_FORMAT(DATE_ADD('day', -10, CURRENT_DATE),'%Y%m%d') as bigint) as day,
DATE_ADD('day', -10, CURRENT_DATE) as date为了提高性能,我建议根据日期和数据大小对表进行分区,同时根据国家代码,并将计算的日期作为参数传递,而不是作为子查询,以确保分区修剪工作。
https://stackoverflow.com/questions/55637874
复制相似问题