Conditional -Merge

Question

Version 23.2.
Suppose I have the following objects.

CREATE TABLE BASE
(
  dt        Date
, a         AggregateFunction (sum,  Float64)
, d         AggregateFunction (uniq, UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY dt
;

CREATE MATERIALIZED VIEW BASE_MV 
TO BASE_MV_DEST
AS
SELECT 
  t.dt                  as dt
, sumState  (t.a)       as a
, uniqState (t.d)       as d
FROM BASE t
GROUP BY t.dt
;

What I'd like to get is conditional -Merge functions like below.

SELECT 
  sumMerge  (if (dt = today(), a, a*0)) a
-- no way to get the same workaround
-- Code: 43. DB::Exception: Nested type AggregateFunction(uniq, UInt32) 
-- cannot be inside Nullable type:  
, uniqMerge (if (dt = today(), d, null)) d_needed
-- ...
FROM BASE_MV_DEST
WHERE dt between addMonths (today(), -1) and today()

The workaround could be using an additional GROUP BY dt in a sub-query, but I'd like to avoid it - conditional expressions may use different columns.
I was able to find a workaround for sumMerge, but not for uniqMerge.

Is this possible?

Answer 1

-if combinator

SELECT
    sumMergeIf(a, dt = today()) AS a,
    uniqMergeIf(d, dt = today()) AS d
FROM
(
    SELECT
        today() AS dt,
        sumState(100) AS a,
        arrayReduce('uniqState', ['a', 'b', 'c']) AS d
    UNION ALL
    SELECT
        today() - 1 AS dt,
        sumState(10) AS a,
        arrayReduce('uniqState', ['c', 'd']) AS d
)

┌───a─┬─d─┐
│ 100 │ 3 │
└─────┴───┘

---

emptyState (much slower)

WITH
    initializeAggregation('sumStateIf', 0, 0) AS emptySumState,
    initializeAggregation('uniqStateIf', '', 0) AS emptyUniqState
SELECT
    sumMerge(if(dt = today(), a, emptySumState)) AS a,
    uniqMerge(if(dt = today(), d, emptyUniqState)) AS d
FROM
(
    SELECT
        today() AS dt,
        sumState(100) AS a,
        arrayReduce('uniqState', ['a', 'b', 'c']) AS d
    UNION ALL
    SELECT
        today() - 1 AS dt,
        sumState(10) AS a,
        arrayReduce('uniqState', ['c', 'd']) AS d
)

┌───a─┬─d─┐
│ 100 │ 3 │
└─────┴───┘