我有一张table，看起来像：

usr_id  query_ts
12345   2019/05/13 02:06
123444  2019/05/15 04:06
123444  2019/05/16 05:06
12345   2019/05/16 02:06
12345   2019/05/15 02:06

它包含用户运行查询时的用户id。表中的每个条目表示以给定的时间戳运行1个查询的id。
我正在尝试制作：

usr_id  day_1   day_2   …   day_30
12345   31       13           15
123444  23       41           14

我想显示过去30天内每天为每个id运行的查询数，如果当天没有运行任何查询，则为0。
这是我提出的问题的一部分，

SELECT
t1.usr_id,
case when t1.count_day_1 is null then 0 else t1.count_day_1 end as day_1,
case when t2.count_day_2 is null then 0 else t2.count_day_2 end as day_2
FROM

(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_1,
        COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_1
        FROM db.table
        WHERE
            DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 1
        AND
            from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
                BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
                AND from_unixtime(unix_timestamp())
        GROUP BY usr_id, day_1) t1

LEFT JOIN
(SELECT usr_id, DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) as day_2,
        COUNT( DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd"))) as count_day_2
        FROM db.table
        WHERE
            DAY(from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")) = 2
        AND
            from_unixtime(unix_timestamp(query_ts ,"yyyy/MM/dd"), "yyyy-MM-dd")
                BETWEEN date_sub(from_unixtime(unix_timestamp()), 30)
                AND from_unixtime(unix_timestamp())
        GROUP BY usr_id, day_2) t2
ON (t1.usr_id = t2.usr_id)
ORDER BY t1.usr_id;

这非常有效，它显示了前两天每天运行的查询数，并用0替换空值。
问题是要让它在30天内正常工作，我必须使用30个左连接，它在集群上占用了~400gb+的内存。
有没有更简单的方法？