<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ryan Zhang on Medium]]></title>
        <description><![CDATA[Stories by Ryan Zhang on Medium]]></description>
        <link>https://medium.com/@dirtysalt?source=rss-1d15a805941f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*CiwkNNgu8A53SvEu.jpg</url>
            <title>Stories by Ryan Zhang on Medium</title>
            <link>https://medium.com/@dirtysalt?source=rss-1d15a805941f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 08:16:08 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dirtysalt/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[How to use UDWF(User Defined Window Function) to compute user retention in StarRocks]]></title>
            <link>https://medium.com/@dirtysalt/how-to-use-udwf-user-defined-window-function-to-compute-user-retention-in-starrocks-c7ad6822277?source=rss-1d15a805941f------2</link>
            <guid isPermaLink="false">https://medium.com/p/c7ad6822277</guid>
            <dc:creator><![CDATA[Ryan Zhang]]></dc:creator>
            <pubDate>Sat, 15 Jul 2023 05:11:20 GMT</pubDate>
            <atom:updated>2023-07-15T05:16:57.271Z</atom:updated>
            <cc:license>https://creativecommons.org/publicdomain/mark/1.0/</cc:license>
            <content:encoded><![CDATA[<h4>How UDWF Works</h4><h4>Problem definition</h4><p>Suppose the user’s table schema is as follows:</p><ul><li>uid represents user id</li><li>date indicates that the user is active on this day</li></ul><p>We create a StarRocks table like this:</p><pre>create table user_activity (uid int, date string)<br>    engine = olap duplicate key (`uid`)<br>    distributed by hash(`uid`)<br>    properties (&quot;replication_num&quot; = &quot;1&quot;);</pre><p>For user retention, we need to calculate the number of active users on T day, and the number of active days in T + 1, T + 2, T + 3, etc</p><h4>Ingest test data</h4><p>Use python script to generate test data</p><pre>def main():<br>    data = []<br>    today = datetime.date.today()<br>    for user_id in range(3):<br>        for d in range(10):<br>            if random.random() &lt; 0.5:<br>                date = today - datetime.timedelta(days=d)<br>                data.append((date.strftime(&#39;%Y-%m-%d&#39;), user_id))<br><br>    with open(&#39;user_activity.csv&#39;, &#39;w&#39;, newline=&#39;&#39;) as csvfile:<br>        fieldnames = [&#39;date&#39;, &#39;user_id&#39;]<br>        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)<br><br>        # writer.writeheader()<br>        for row in data:<br>            writer.writerow({&#39;date&#39;: row[0], &#39;user_id&#39;: row[1]})<br><br>main()</pre><p>Then use stream load to import these data into StarRocks</p><pre>curl --location-trusted -u root: -H &quot;label:124&quot; \<br>    -H &quot;column_separator:,&quot; \<br>    -H &quot;columns: date,uid&quot; \<br>    -T user_activity.csv -XPUT \<br>    <a href="http://127.0.0.1:41001/api/zya/user_activity/_stream_load">http://127.0.0.1:41001/api/zya/user_activity/_stream_load</a></pre><p>And we can see data in this table.</p><pre>MySQL [zya]&gt; select * from user_activity;<br>+------+------------+<br>| uid  | date       |<br>+------+------------+<br>|    0 | 2023-07-10 |<br>|    0 | 2023-07-09 |<br>|    0 | 2023-07-08 |<br>|    0 | 2023-07-06 |<br>|    0 | 2023-07-05 |<br>|    1 | 2023-07-12 |<br>|    1 | 2023-07-10 |<br>|    1 | 2023-07-09 |<br>|    1 | 2023-07-07 |<br>|    1 | 2023-07-05 |<br>|    1 | 2023-07-04 |<br>|    2 | 2023-07-12 |<br>|    2 | 2023-07-10 |<br>|    2 | 2023-07-08 |<br>|    2 | 2023-07-06 |<br>|    2 | 2023-07-03 |<br>+------+------------+</pre><h4>Write SQL to compute retention</h4><p>Here we need to use the window function <a href="https://docs.starrocks.io/en-us/latest/sql-reference/sql-functions/Window_function">Window function @Window_function @StarRocks Docs</a> to compute retention</p><p>But the window function has a limitation, that is a row of data can only output one value, but we may want to know the retention of T + 1, T + 2… these days after T, which means we have to output many values for a single row.</p><p>So here we use a bit trick to calculate the retention of up to 63 days. We use a long field encoded to encode:</p><ul><li>Assuming that the uid is active on T day, then set at bit 0 encoded | = (1 &lt;&lt; 0)</li><li>Assuming T + x days uid is still active, then encoded | = (1 &lt;&lt; x)</li></ul><p>Then we use different bit right shift to count the retention of different days. In the SQL, RetentionEncode is the UDWF (user defined window function) we want to implement.</p><pre>with Rte as (select uid, date as date , RetentionEncode(date)  over (partition by uid order by date desc) as encoded from user_activity)<br>     select date, count(1) as cohort, <br>          sum(bitand(bit_shift_right(encoded, 1), 1)) as day1, <br>          sum(bitand(bit_shift_right(encoded, 2), 1)) as day2 from Rte <br>          group by date order by date;</pre><h4>Test UDWF</h4><p>Use the global function mechanism to test this UDWF into SR</p><p><a href="https://docs.starrocks.io/en-us/latest/sql-reference/sql-functions/JAVA_UDF#java-udfs">Java UDFs @ JAVA_UDF</a></p><pre>drop global function RetentionEncode(string);<br><br>CREATE GLOBAL AGGREGATE FUNCTION RetentionEncode(string)<br>RETURNS bigint<br>properties<br>(<br>    &quot;analytic&quot; = &quot;true&quot;,<br>    &quot;symbol&quot; = &quot;com.starrocks.udf.retentionEncode&quot;,<br>    &quot;type&quot; = &quot;StarrocksJar&quot;,<br>    &quot;file&quot; = &quot;http://127.0.0.1:41006/data/sr-udf-1.0-SNAPSHOT-jar-with-dependencies.jar&quot;<br>);</pre><p>Then let’s look at the retention for 1 or 2 days</p><pre>MySQL [zya]&gt; select * from user_activity;<br>+------+------------+<br>| uid  | date       |<br>+------+------------+<br>|    0 | 2023-07-10 |<br>|    0 | 2023-07-09 |<br>|    0 | 2023-07-08 |<br>|    0 | 2023-07-06 |<br>|    0 | 2023-07-05 |<br>|    1 | 2023-07-12 |<br>|    1 | 2023-07-10 |<br>|    1 | 2023-07-09 |<br>|    1 | 2023-07-07 |<br>|    1 | 2023-07-05 |<br>|    1 | 2023-07-04 |<br>|    2 | 2023-07-12 |<br>|    2 | 2023-07-10 |<br>|    2 | 2023-07-08 |<br>|    2 | 2023-07-06 |<br>|    2 | 2023-07-03 |<br>+------+------------+<br>16 rows in set (0.070 sec)<br><br>MySQL [zya]&gt; with Rte as (select uid, date as date , RetentionEncode(date)  over (partition by uid order by date desc) as encoded from user_activity)    select date, count(1) as cohort, sum(bitand(bit_shift_right(encoded, 1), 1)) as day1, sum(bitand(bit_shift_right(encoded, 2), 1)) as day2 from Rte group by date order by date;<br>+------------+--------+------+------+<br>| date       | cohort | day1 | day2 |<br>+------------+--------+------+------+<br>| 2023-07-03 |      1 |    0 |    0 |<br>| 2023-07-04 |      1 |    1 |    0 |<br>| 2023-07-05 |      2 |    1 |    1 |<br>| 2023-07-06 |      2 |    0 |    2 |<br>| 2023-07-07 |      1 |    0 |    1 |<br>| 2023-07-08 |      2 |    1 |    2 |<br>| 2023-07-09 |      2 |    2 |    0 |<br>| 2023-07-10 |      3 |    0 |    2 |<br>| 2023-07-12 |      2 |    0 |    0 |<br>+------------+--------+------+------+<br>9 rows in set (0.385 sec)</pre><p>Here to verify the effect, take the day 07–08 as an example</p><ul><li>On the day 07–08, uid = 0, 2 was active , so cohort = 2</li><li>On the day 07–09, uid = 0 was active, so day1 = 1</li><li>On the day 07–10, uid = 0, 2 was active, so day2 = 2</li></ul><h4>UDWF Code</h4><p><a href="https://docs.starrocks.io/en-us/latest/sql-reference/sql-functions/JAVA_UDF#use-a-udwf">Java UDFs @ JAVA_UDF</a></p><p>The code is as follows, and the general idea is:</p><ul><li>We will see a series of dates, which are arranged in descending order</li><li>We first see T + x, and then after we see T</li><li>Use TreeSet to maintain all dates within 63 days</li><li>Traverse the TreeSet to generate the corresponding encoded</li></ul><pre>package com.starrocks.udf;<br>    <br>import java.text.SimpleDateFormat;<br>import java.util.Date;<br>import java.util.TreeSet;<br>import java.util.concurrent.TimeUnit;<br><br>public class retentionEncode {<br>    public static class State {<br>        TreeSet&lt;Long&gt; buffer = new TreeSet&lt;&gt;();<br><br>        Long encoded = 0L;<br><br>        @Override<br>        public String toString() {<br>            return String.format(&quot;State(encoded = 0x%x, buffer = %d)&quot;, encoded, buffer.size());<br>        }<br><br>        public int serializeLength() {<br>            return 4;<br>        }<br>    }<br><br>    public State create() {<br>        return new State();<br>    }<br><br>    public void destroy(State state) {<br><br>    }<br><br>    public void update(State state, String val) {<br>    }<br><br>    public void serialize(State state, java.nio.ByteBuffer buff) {<br>    }<br><br>    public void merge(State state, java.nio.ByteBuffer buffer) {<br>    }<br><br>    public Long finalize(State state) {<br>        System.out.println(&quot;finalize: &quot; + state.toString());<br>        return state.encoded;<br>    }<br><br>    public void reset(State state) {<br>        System.out.println(&quot;reset: &quot; + state.toString());<br>        state.buffer.clear();<br>        state.encoded = 0L;<br>    }<br><br>    private static Date stringToDate(String val) {<br>        SimpleDateFormat dateFormat = new SimpleDateFormat(&quot;yyyy-MM-dd&quot;);<br>        try {<br>            Date date = dateFormat.parse(val);<br>            return date;<br>        } catch (Exception e) {<br>            e.printStackTrace();<br>            return null;<br>        }<br>    }<br><br>    private void updateEncoded(State state, Date val) {<br>        state.encoded = 0L;<br>        if (val == null) {<br>            return;<br>        }<br><br>        long now = val.getTime();<br>        state.buffer.add(now);<br><br>        final int maxDays = 8;<br><br>        // pop out date &gt;= maxDays<br>        while (state.buffer.size() &gt; 0) {<br>            long last = state.buffer.last();<br>            long diffInMs = last - now;<br>            long diffInDays = TimeUnit.DAYS.convert(diffInMs, TimeUnit.MILLISECONDS);<br>            if (diffInDays &gt;= maxDays) {<br>                state.buffer.pollLast();<br>            } else {<br>                break;<br>            }<br>        }<br><br>        for (long t : state.buffer) {<br>            long diffInMs = t - now;<br>            long diffInDays = TimeUnit.DAYS.convert(diffInMs, TimeUnit.MILLISECONDS);<br>            assert diffInDays &lt; maxDays;<br>            state.encoded |= (1L &lt;&lt; diffInDays);<br>        }<br>    }<br><br>    public void windowUpdate(State state, int peer_group_start, int peer_group_end, int frame_start, int frame_end,<br>                             String[] dates) {<br>        Date val = stringToDate(dates[frame_start]);<br>        updateEncoded(state, val);<br>    }<br>}</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c7ad6822277" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>