emerkfu commented on issue #11560:
URL: https://github.com/apache/doris/issues/11560#issuecomment-1208856270
> Hi, did you use these array functions before? If yes, what database did
you use these functions on before? Hope to be provided more use cases, thanks.
I use the above three functions on ClickHouse.
Below is an example of how we used ClickHouse to calculate conversion rates
at each level of an ordered funnel.
```
1. The funnel process
login_in -> $pageview -> $end -> $startup -> $end -> $startup
2. Time range 2021-07-01 ~ 2021-07-09
3. Conversion cycle 1 day
```
```
CREATE TABLE IF NOT EXISTS test.`event`(
`distinct_id` Nullable(Int64),
`xwhen` Nullable(Int64),
`xwhat` Nullable(String),
`screen_width` Nullable(decimal64(3))
) engine = TinyLog;
SELECT level_index,count(1) FROM
(
SELECT distinct_id,
arrayWithConstant(level, 1) levels,
arrayEnumerate( levels ) b, -- for_test_col
arrayJoin(arrayEnumerate( levels )) level_index
FROM (
SELECT
distinct_id,
windowFunnel(86400)(
time,
xwhat = 'login_in',
xwhat = '$pageview',
xwhat = '$end',
xwhat = '$startup',
xwhat = '$end',
xwhat = '$startup'
) AS level
FROM (
SELECT
distinct_id
, xwhat
, toDateTime(toUInt64(round(xwhen/1000))) as time
FROM test.event
WHERE (toDate(time) >= '2021-07-01' and toDate(time) <=
'2021-07-09' and xwhat = 'login_in')
or (toDate(time) >= '2021-07-01' and toDate(time) <= '2021-07-10'
and xwhat in ('$pageview','$end','$startup'))
)
GROUP BY distinct_id
)
)
group by level_index
ORDER BY level_index;
```
--
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: commits-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
