在MySQL中聚合JSON数组和计算集合联合大小

Question

我有一个用例，需要计算任意时间段的集合重叠。

我的数据是这样的，当我被装进熊猫的时候。在MySQL中，user_ids与数据类型JSON一起存储。

​

​

当按date列分组时，我需要计算联合集的大小。例如，在下面的示例中，如果2021-01-31与2021-02-28一起分组，则结果应该是

In [1]: len(set([46, 44, 14] + [44, 7, 36]))
Out[1]: 5

用Python做这件事很简单，但我很难在MySQL中做到这一点。

将数组聚合到数组中很容易：

SELECT 
    date,
    JSON_ARRAYAGG(user_ids) as uids
FROM mytable
GROUP BY date

​

​

但在那之后，我面临两个问题：

1. 如何将数组的数组扁平为单个数组
2. 如何提取不同的值(例如，将数组转换为一个集合)

有什么建议吗？谢谢!

PS。在我的例子中，我可能可以通过在客户端进行扁平化和设置转换来度过难关，但是我很惊讶像这样简单的事情竟然是如此的困难.:/

Answer 1

正如其他注释中提到的，在数据库中存储JSON数组确实不是最优的，应该避免。除此之外，首先提取JSON数组更容易(并从第二点获得您想要的结果)：

SELECT mytable.date, jtable.VAL as user_id
FROM mytable, JSON_TABLE(user_ids, '$[*]' COLUMNS(VAL INT PATH '$')) jtable;

从现在开始，我们可以再次对日期进行分组，并将user_ids与您已经找到的JSON_ARRAYAGG函数重新组合到JSON数组中：

SELECT mytable.date, JSON_ARRAYAGG(jtable.VAL) as user_ids
FROM mytable, JSON_TABLE(user_ids, '$[*]' COLUMNS(VAL INT PATH '$')) jtable
GROUP BY mytable.date;

您可以在这个DB小提琴中试用这种方法。

注意:这确实需要mysql 8+/mariaDB 10.6+。

Answer 2

谢谢你的回答。

对于任何感兴趣的人来说，我最终得到的解决方案是像这样存储数据：

​

​

然后对熊猫进行设定计算。

(
    df.groupby(pd.Grouper(key="date", freq="QS"),).aggregate(
        num_unique_users=(
            "user_ids",
            lambda uids: len(set([_ for ul in uids for _ in ul])),
        ),
    )
)

我能够将20 and表减少到大约300 and，这足够快地查询和检索数据。