معدِّلات الدوال التجميعية

يمكن إلحاق لاحقة باسم الدالة التجميعية. وهذا يغيّر طريقة عملها.

يمكن إلحاق اللاحقة -If باسم أي دالة تجميعية. في هذه الحالة، تقبل الدالة التجميعية وسيطة إضافية — شرطًا (من النوع Uint8). ولا تعالج الدالة التجميعية إلا الصفوف التي تحقق الشرط. وإذا لم يتحقق الشرط ولو مرة واحدة، فإنها تُرجع قيمة افتراضية (عادةً أصفارًا أو سلاسل نصية فارغة).

أمثلة: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) وما إلى ذلك.

باستخدام الدوال التجميعية الشرطية، يمكنك حساب القيم المجمعة لعدة شروط في الوقت نفسه، من دون استخدام الاستعلامات الفرعية وعمليات JOIN. على سبيل المثال، يمكن استخدام الدوال التجميعية الشرطية لتنفيذ ميزة مقارنة الشرائح.

يمكن إلحاق اللاحقة -Array بأي دالة تجميع. في هذه الحالة، تأخذ دالة التجميع وسيطات من النوع 'Array(T)' (مصفوفات) بدلًا من وسيطات من النوع 'T'. وإذا كانت دالة التجميع تقبل عدة وسيطات، فيجب أن تكون هذه الوسيطات مصفوفات ذات أطوال متساوية. وعند معالجة المصفوفات، تعمل دالة التجميع بالطريقة نفسها التي تعمل بها دالة التجميع الأصلية على جميع عناصر المصفوفة.

مثال 1: sumArray(arr) - يجمع إجمالي جميع عناصر كل مصفوفات 'arr'. في هذا المثال، كان يمكن كتابة ذلك بصورة أبسط: sum(arraySum(arr)).

مثال 2: uniqArray(arr) – يحسب عدد العناصر الفريدة في جميع مصفوفات 'arr'. ويمكن تنفيذ ذلك بطريقة أسهل: uniq(arrayJoin(arr))، لكن لا يكون من الممكن دائمًا إضافة 'arrayJoin' إلى استعلام.

يمكن الجمع بين ‎-If و -Array. ومع ذلك، يجب أن تأتي 'Array' أولًا ثم 'If'. أمثلة: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). وبسبب هذا الترتيب، لن تكون الوسيطة 'cond' مصفوفة.

يمكن إلحاق اللاحقة -Map بأي aggregate function. ويؤدي ذلك إلى إنشاء aggregate function تستقبل Map type كـ argument، وتُجمِّع values الخاصة بكل key في الـ map كلًّا على حدة باستخدام aggregate function المحددة. وتكون النتيجة أيضًا من Map type.

مثال

CREATE TABLE map_map(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO map_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));

SELECT
    timeslot,
    sumMap(status),
    avgMap(status),
    minMap(status)
FROM map_map
GROUP BY timeslot;

┌

إذا طبّقت هذا الـ combinator، فستُرجِع aggregate function القيمة نفسها ولكن بنوع مختلف. ويكون هذا SimpleAggregateFunction(…) قابلاً للتخزين في table للعمل مع جداول AggregatingMergeTree.

الصياغة

<aggFunction>SimpleState(x)

الوسائط

• x — معلمات الدالة التجميعية.

القيم المُعادة

قيمة دالة تجميعية من النوع SimpleAggregateFunction(...).

مثال

WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);

┌─toTypeName(c)────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────┴───┘

إذا طبّقت هذا combinator، فلن تُرجِع الدالة التجميعية القيمةَ الناتجة (مثل عدد القيم الفريدة للدالة uniq)، بل تُرجِع حالةً وسيطة من عملية التجميع (وبالنسبة إلى uniq، فهذه هي hash table المستخدمة لحساب عدد القيم الفريدة). وهذا AggregateFunction(...) يمكن استخدامه لمزيد من المعالجة أو تخزينه في جدول لإكمال التجميع لاحقًا.

للعمل مع هذه الحالات، استخدم:

• محرك الجدول AggregatingMergeTree.
• الدالة finalizeAggregation.
• الدالة runningAccumulate.
• combinator ‏-Merge.
• combinator ‏-MergeState.

إذا طبّقت هذا المُركِّب، فإن دالة التجميع تأخذ حالة التجميع الوسيطة كوسيطة، ثم تدمج الحالات لاستكمال التجميع وتُرجِع القيمة الناتجة.

يدمج حالات التجميع الوسيطة بالطريقة نفسها التي يعمل بها المُركِّب -Merge. لكنه لا يعيد القيمة الناتجة، بل يعيد حالة تجميع وسيطة، على غرار المُركِّب -State.

يحوّل دالة تجميع تُستخدم مع الجداول إلى دالة تجميع للمصفوفات، بحيث تُجمِّع العناصر المناظرة في المصفوفات وتُرجع مصفوفة من النتائج. على سبيل المثال، تُرجع sumForEach للمصفوفات [1, 2] و[3, 4, 5] و[6, 7] النتيجة [10, 13, 5] بعد جمع العناصر المناظرة معًا.

يمكن إلحاق اللاحقة -Tuple بأي دالة تجميعية. تأخذ الدالة الناتجة وسيطًا واحدًا من النوع Tuple مقابل كل وسيط في الدالة التجميعية الأساسية، ويجب أن تحتوي جميع قيم Tuple على العدد نفسه من العناصر. ويُطبَّق التجميع بصورة مستقلة على موضع كل عنصر، بحيث يستقبل العنصر المناظر من كل Tuple، ويُرجع Tuple من النتائج.

إذا كان Tuple الأول في الإدخال يحتوي على أسماء صريحة للعناصر، فستُحفَظ هذه الأسماء في النتيجة.

الدوال التجميعية التي تتعامل مع قيم NULL بنفسها (anyRespectNulls وanyLastRespectNulls والمُعدِّل RESPECT NULLS) لا تدعم النوع Nullable(Tuple(...)) كوسيط؛ استخدم عناصر Nullable بدلًا من ذلك.

الصياغة

<aggFunction>Tuple(tuple1[, tuple2, ...])

الوسائط

• tuple1[, tuple2, ...] — أعمدة من النوع Tuple، عمود واحد لكل وسيط من وسائط دالة التجميع الأساسية، على أن يكون لها جميعًا العدد نفسه من العناصر. ويجب أن يكون كل عنصر من نوع تدعمه دالة التجميع الأساسية في موضع ذلك الوسيط.

القيم المُعادة

• قيمة Tuple تحتوي على نتيجة تطبيق دالة التجميع على كل عنصر بشكل مستقل.

النوع: Tuple(aggFunction(element1), aggFunction(element2), ...).

مثال

الاستعلام:

SELECT sumTuple(t) FROM
(
    SELECT tuple(toInt64(1), toFloat64(2.5)) AS t
    UNION ALL
    SELECT tuple(toInt64(3), toFloat64(4.5))
    UNION ALL
    SELECT tuple(toInt64(5), toFloat64(6.5))
);

النتيجة:

┌─sumTuple(t)─┐
│ (9,13.5)    │
└─────────────┘

عند الاستخدام مع GROUP BY:

SELECT
    k,
    avgTuple(t)
FROM
(
    SELECT
        number % 2 AS k,
        tuple(toInt64(number), toFloat64(number) * 1.5) AS t
    FROM numbers(6)
)
GROUP BY k
ORDER BY k;

┌─k─┬─avgTuple(t)─┐
│ 0 │ (2,3)       │
│ 1 │ (3,4.5)     │
└───┴─────────────┘

عند استخدامه مع دالة تجميع متعددة الوسائط: يمرّر كل وسيط Tuple وسيطًا واحدًا إلى الدالة الأساسية، وتُطابَق العناصر بحسب ترتيبها:

corrTuple((a1, a2), (b1, b2)) = (corr(a1, b1), corr(a2, b2))

SELECT corrTuple((a1, a2), (b1, b2))
FROM
(
    SELECT
        toFloat64(number) AS a1,
        toFloat64(number * 2) AS a2,
        toFloat64(100 - number) AS b1,
        toFloat64(number * 3) AS b2
    FROM numbers(10)
);

┌─corrTuple((a1, a2), (b1, b2))─┐
│ (-1,1)                        │
└───────────────────────────────┘

a1 و b1 مرتبطان عكسيًا، بينما a2 و b2 يتناسبان طرديًا، لذا تكون النتيجة (-1, 1).

يمكن دمج -Tuple مع مُركِّبات أخرى مثل -If. على سبيل المثال: sumTupleIf(tuple_column, cond).

لن يُحتسب كل تجميع لكل تركيبة فريدة من الوسائط إلا مرة واحدة فقط. وتُتجاهل القيم المتكررة. أمثلة: sum(DISTINCT x) (أو sumDistinct(x))، وgroupArray(DISTINCT x) (أو groupArrayDistinct(x))، وcorrStable(DISTINCT x, y) (أو corrStableDistinct(x, y)) وهكذا.

يغيّر سلوك دالة تجميع.

إذا لم تكن لدالة التجميع قيم إدخال، فإن هذا المُركِّب يجعلها تُرجع القيمة الافتراضية لنوع بيانات الإرجاع الخاص بها. ينطبق ذلك على دوال التجميع التي يمكنها قبول بيانات إدخال فارغة.

يمكن استخدام -OrDefault مع مُركِّبات أخرى.

الصيغة

<aggFunction>OrDefault(x)

المعاملات

• x — معلمات دالة التجميع.

القيم المُعادة

تعيد القيمة الافتراضية لنوع الإرجاع الخاص بدالة التجميع إذا لم تكن هناك قيم لتجميعها.

يعتمد النوع على دالة التجميع المستخدمة.

مثال

SELECT avg(number), avgOrDefault(number) FROM numbers(0)

┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

يمكن أيضًا استخدام -OrDefault مع combinators أخرى. ويكون ذلك مفيدًا عندما لا تقبل aggregate function مدخلات فارغة.

SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘

يغيّر سلوك دالة تجميعية.

يحوّل هذا المُركِّب ناتج الدالة التجميعية إلى نوع البيانات Nullable. وإذا لم تتوفر للدالة التجميعية قيم لإجراء الحساب عليها، فإنها تُرجع NULL.

يمكن استخدام -OrNull مع مُركِّبات أخرى.

البنية

<aggFunction>OrNull(x)

الوسيطات

• x — وسيطات الدالة التجميعية.

القيم المُعادة

• ناتج الدالة التجميعية بعد تحويله إلى نوع البيانات Nullable.
• NULL إذا لم تكن هناك قيم لتجميعها.

النوع: Nullable(aggregate function return type).

مثال

أضف -orNull إلى نهاية اسم الدالة التجميعية.

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10

┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

يمكن أيضًا استخدام -OrNull مع مُركِّبات أخرى. ويكون ذلك مفيدًا عندما لا تقبل الدالة التجميعية مُدخلًا فارغًا.

SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

يتيح لك تقسيم البيانات إلى مجموعات، ثم تجميع البيانات داخل تلك المجموعات كلٌّ على حدة. تُنشأ المجموعات بتقسيم قيم عمود واحد إلى فترات.

<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)

الوسيطات

• start — قيمة بداية النطاق الزمني الكامل المطلوب لقيم resampling_key.
• stop — قيمة نهاية النطاق الزمني الكامل المطلوب لقيم resampling_key. لا يشمل النطاق الزمني الكامل القيمة stop ‏[start, stop).
• step — الخطوة المستخدمة لتقسيم النطاق الزمني الكامل إلى نطاقات زمنية فرعية. تُنفَّذ aggFunction على كل نطاق زمني فرعي من هذه النطاقات بشكل مستقل.
• resampling_key — العمود الذي تُستخدم قيمه لتقسيم البيانات إلى نطاقات زمنية.
• aggFunction_params — معاملات aggFunction.

القيم المُعادة

• مصفوفة من نتائج aggFunction لكل نطاق زمني فرعي.

مثال

لنفترض الجدول people الذي يحتوي على البيانات التالية:

┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘

لنحصل على أسماء الأشخاص الذين تقع أعمارهم ضمن المجالين [30,60) و[60,75). وبما أننا نستخدم الأعداد الصحيحة لتمثيل العمر، فإن الأعمار تقع ضمن المجالين [30, 59] و[60,74].

لتجميع الأسماء في مصفوفة، نستخدم الدالة التجميعية groupArray. وهي تأخذ وسيطًا واحدًا. في حالتنا، هذا هو العمود name. ويجب أن تستخدم الدالة groupArrayResample العمود age لتجميع الأسماء حسب العمر. ولتحديد المجالات المطلوبة، نمرّر الوسائط 30, 75, 30 إلى الدالة groupArrayResample.

SELECT groupArrayResample(30, 75, 30)(name, age) FROM people

┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘

تأمّل النتائج.

John خارج العينة لأنه أصغر من الفئة العمرية المطلوبة. أمّا الأشخاص الآخرون فتوزيعهم يكون وفقًا للفترات العمرية المحددة.

والآن، لنحسب العدد الإجمالي للأشخاص ومتوسط أجورهم ضمن الفترات العمرية المحددة.

SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people

┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘

يمكن إلحاق اللاحقة -ArgMin باسم أي دالة تجميعية. في هذه الحالة، تقبل الدالة التجميعية وسيطة إضافية يمكن أن تكون أي تعبير قابل للمقارنة. ولا تعالج الدالة التجميعية إلا الصفوف التي تحمل الحد الأدنى من القيمة للتعبير الإضافي المحدد.

أمثلة: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) وما إلى ذلك.

تشبه اللاحقة -ArgMin، لكنها تعالج فقط الصفوف التي تحتوي على القيمة القصوى للتعبير الإضافي المحدد.

• مدونة: استخدام مُبدِّلات التجميع في ClickHouse