xy720 opened a new pull request #6466:
URL: https://github.com/apache/incubator-doris/pull/6466
## Proposed changes
```
SELECT event_day
, SUM(if(medal_level = 'continuous_login', cum_medal_user_cnt, 0)) AS
continuous_login_cum_user_cnt
, SUM(if(medal_level = 'koubei', cum_medal_user_cnt, 0)) AS
koubei_cum_user_cnt
, SUM(if(medal_level = 'wenda_answer', cum_medal_user_cnt, 0)) AS
wenda_answer_cum_user_cnt
, SUM(if(medal_level = 'wenda_question', cum_medal_user_cnt, 0)) AS
wenda_question_cum_user_cnt
, SUM(if(medal_level = 'time', cum_medal_user_cnt, 0)) AS
time_cum_user_cnt
FROM (
SELECT medal_data.event_day, medal_data.medal_level, COUNT(DISTINCT
medal_data.uid) AS medal_user_cnt
, COUNT(DISTINCT if(row_rank = 1, medal_data.uid, NULL)) AS
medal_first_user_cnt
, SUM(COUNT(DISTINCT if(row_rank = 1, medal_data.uid, NULL)))
OVER (PARTITION BY medal_level ORDER BY medal_data.event_day) AS
cum_medal_user_cnt
FROM (
SELECT data_all.event_day, data_all.medal_level, t.uid,
t.row_rank
FROM (
SELECT event_day, medal_level
FROM (
SELECT 1 AS line, event_day
FROM dwd_youjia_medal_new_incr_day
WHERE event_day BETWEEN '2021-07-16' AND
'2021-08-15'
GROUP BY event_day
) date_all
JOIN (
SELECT 1 AS line, medal_level
FROM dwd_youjia_medal_new_incr_day
WHERE event_day BETWEEN '2021-07-16'
AND '2021-08-15'
GROUP BY medal_level
) madel_all
ON date_all.line = madel_all.line
) data_all
LEFT JOIN (
SELECT 1 AS line, event_day, medal_level, uid,
row_number() OVER (PARTITION BY uid, medal_level ORDER BY event_day) AS row_rank
FROM dwd_youjia_medal_new_incr_day
WHERE event_day BETWEEN '2021-07-16' AND
'2021-08-15'
AND 1 = 1
AND 1 = 1
AND medal_level NOT IN ('first_login')
GROUP BY event_day, uid, medal_level
) t
ON t.event_day = data_all.event_day
AND t.medal_level = data_all.medal_level
) medal_data
LEFT JOIN (
SELECT event_day, uid
FROM autocar_youjia_app_ubc_log_new
WHERE event_day BETWEEN '2021-07-16' AND '2021-08-15'
AND os = '全部'
AND bhv_id IN (61, 18, 691)
GROUP BY uid, event_day
) ubc_data
ON medal_data.uid = ubc_data.uid
AND medal_data.event_day = ubc_data.event_day
WHERE if('全部' = '全部', 1 = 1, ubc_data.uid IS NOT NULL)
GROUP BY medal_data.event_day, medal_data.medal_level
) medal_cum_data
GROUP BY event_day
LIMIT 0, 5000;
```
## Types of changes
What types of changes does your code introduce to Doris?
_Put an `x` in the boxes that apply_
- [x] Bugfix (non-breaking change which fixes an issue)
## Checklist
_Put an `x` in the boxes that apply. You can also fill these out after
creating the PR. If you're unsure about any of them, don't hesitate to ask.
We're here to help! This is simply a reminder of what we are going to look for
before merging your code._
- [x] I have created an issue on (Fix #6462 ) and described the bug/feature
there in detail
- [x] Compiling and unit tests pass locally with my changes
## Further comments
If this is a relatively large or complex change, kick off the discussion at
[email protected] by explaining why you chose the solution you did and what
alternatives you considered, etc...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
