Analyzing Data in Apache Doris

Course README · Open Lab 5 · Open Quiz 5

Module goal

Turn event detail into an analytical result whose meaning you can defend: define the result first, then choose functions by the work they do to rows, and build the query in stages whose grain stays visible.

An operations team wants one row per date and region, with event volume, active users, purchasing users, purchase revenue, the previous day's revenue, cumulative revenue, and the region's rank on that date. Lab 5 builds that report from events_modelled, the event table of Module 4.

Learning objectives

After completing this module, you will be able to:

1. Choose a function category, such as scalar, aggregate, window, Table Function, or TVF, from its inputs, outputs, and row-count behavior.
2. Define the population of each measure with conditional aggregates, and filter input rows with WHERE and groups with HAVING.
3. Use ANY_VALUE only when any representative value satisfies the meaning of the group.
4. Name each stage of an analysis with a CTE and state the grain of its result.
5. Choose window partitions, ordering, frames, and ranking functions for previous-value, cumulative, and ranking questions.
6. Choose between built-in functions, Lambda expressions, Alias Functions, and user-defined functions for missing reusable logic.

Module structure

5.1 Define the Analytical Answer Before Choosing Functions

“Show regional performance” is not yet a query contract. It leaves several questions unresolved:

• Which events belong to the report?
• What does one output row represent?
• Which events contribute to each metric?
• Should dates or regions with no qualifying events appear?
• What sequence defines “previous,” “cumulative,” and “rank”?
• Is the result for interactive inspection or for a downstream file workflow?

Resolve these choices before selecting function names. For the scenario in this module, one possible contract is:

This contract prevents superficially plausible mistakes. Grouping only by date would remove the region grain. Filtering to purchases before calculating event_count would turn that column into a purchase-event count. Ranking all 24 rows together would answer a different question from ranking eight regions independently on each date.

Trace the report grain through the query

The query can be understood as a sequence of row shapes:

Trace the meaning and row shape of the result through every analytical stage.

Filtering can remove rows. Grouping usually reduces many detail rows to fewer grouped rows. Window calculation retains the rows it receives and adds a value to each one. Final ordering changes their presentation sequence, not their grain.

Separate result meaning from result delivery

A MySQL-compatible client and a Jupyter Notebook can display the same SQL result in different forms. In the MySQL command-line client, \G requests a vertical display; it is a client command terminator, not part of a query sent through the Notebook helper. Column aliases can improve readability, but they do not change which rows or values the query computes.

When another system needs files, Doris supports SELECT INTO OUTFILE with formats such as comma-separated values (CSV), Parquet, and Optimized Row Columnar (ORC). Export destination, format, permissions, and credentials form a delivery contract. They do not repair an incorrectly defined metric or grain. Lab 5 displays results and performs no export. See SELECT INTO OUTFILE.

5.2 Choose a Function Category from the Required Work

The Doris SQL function catalog is easier to navigate when you first ask what the operation consumes, what it returns, and what happens to the number of rows. The categories are not merely lists of names.

Follow the row shape through the analysis

A useful first approximation connects the core function shapes:

Function categories differ in what they consume, return, and do to row shape.

In shorthand, these are one-to-one, many-to-one, and one-to-many. They describe what the function contributes to the query, not a guarantee that the complete SQL statement preserves that exact row count. A WHERE clause can remove rows, a Join can multiply them, and GROUP BY defines how many aggregate groups remain.

The later sections follow that row-shape progression. Section 5.3 derives values while retaining event rows. Section 5.4 deliberately collapses many events into each daily-region row. Section 5.5 expands one row into several element rows. Section 5.6 then gives those transformations explicit stages, and Section 5.7 uses a window of related rows to calculate one value for every retained result row:

event rows
   |  Scalar: derive event_date
   v
enriched event rows
   |  Aggregate: group by event_date, region
   v
daily-region rows
   |  Window: compare related daily-region rows
   v
the same daily-region rows, now with previous values, totals, or ranks

Some categories describe different dimensions

Row shape and capability source can overlap. AI_SENTIMENT behaves like a per-row scalar calculation, but “AI Function” also tells you that it requires a configured AI Resource and an external model call. AI_AGG instead operates across grouped text. Seeing an AI_* name is therefore not enough to infer its row-count behavior.

Likewise, SUM(revenue) is an Aggregate Function when it summarizes a group. When an aggregate is written with OVER, it follows window semantics and produces a value for every retained input result row. SQL context determines how the operation participates in the result.

Table Functions and TVFs both produce rows, but their starting points differ. A Table Function expands something in a current row; a TVF acts as a table source. Section 5.5 develops that distinction with concrete row shapes.

Treat AI calls as an external dependency

An AI Function is appropriate only after the application has defined the model provider, AI Resource, credentials, network access, latency expectations, failure policy, data-governance rules, and acceptable cost. A sentiment label returned by an external model is not equivalent to a deterministic local string transformation.

The lab classifies AI_SENTIMENT but does not execute it. This keeps the lab self-contained and avoids sending course data to an external provider. Consult the AI Functions overview and the chosen function's current contract before adopting it.

5.3 Use Scalar Functions to Derive Explainable Dimensions

The event table already stores event_time, region, and event_type in typed columns. A report may still need an event date, hour boundary, hour number, or display label. Scalar Functions derive those values independently for each selected input row.

Distinguish time meaning from time presentation

Different time expressions answer different questions:

A formatted string may look like a date, but its contract is still a string. Prefer a temporal value when later operations must compare dates, calculate intervals, or preserve calendar semantics. Format the value when the consumer actually needs a label.

The query's time zone and boundary convention are part of the metric contract. “Daily” is ambiguous if the source timestamp and the business reporting zone differ. A function cannot resolve that business decision by itself.

Separate normalization, labeling, and matching

String operations also serve distinct purposes:

• LOWER, UPPER, and TRIM can normalize values for a stated comparison or presentation rule.
• REPLACE can construct a display label when the transformation is truly mechanical.
• LIKE and REGEXP are predicates that decide which rows qualify; they do not standardize the stored value.

Do not casually normalize an identifier that is case-sensitive by contract. If several spellings represent the same category, decide whether ingestion should establish one canonical value. Repeating cleanup logic across every analytical query can signal that the data boundary is too permissive.

Scalar expressions do not group rows by themselves. If eight event rows enter a projection containing TO_DATE(event_time) and UPPER(region), eight rows can still leave that projection. A later GROUP BY changes the grain.

5.4 Use Aggregate Functions to Answer Defined Metric Questions

An Aggregate Function combines observations. The correct function depends on both the mathematical operation and the population being observed.

Define the population for every measure

Consider four measures in one daily-region row:

The conditional expressions allow measures with different contributing populations to share the same daily-region result grain. Filtering the entire query to purchases would also remove non-purchase events from event_count and active_users, changing their meaning.

Distinct counts are not generally additive. A user active in two regions or on two dates contributes to each relevant group but should appear once in a global distinct-user count. Summing daily-region distinct counts does not produce a globally distinct population.

Choose the statistic that matches the question

COUNT, SUM, AVG, MIN, and MAX answer different questions. An average needs a defined observation and denominator. “Average revenue” might mean average per purchase event, per purchasing user, or per day; these values are not interchangeable.

Variance describes spread in one numeric measure. Covariance describes how two measures vary together in their units. Correlation normalizes that relationship to a dimensionless scale. None establishes causation. Before using a statistical aggregate, define the observation grain, sample or population interpretation, missing-value policy, and whether extreme values should remain in scope.

Put filters at the stage they are meant to affect

WHERE and HAVING operate at different row shapes:

WHERE tests input rows; HAVING tests groups after their aggregates exist.

To find products whose purchase revenue reaches a threshold on one day, WHERE should choose that day's purchase events, while HAVING SUM(revenue) should choose qualifying product groups. A predicate such as WHERE revenue >= 120000 asks whether each individual event reaches the threshold. It does not test the product total.

Use ANY_VALUE only under a real constraint

A grouped query cannot project an ordinary column whose value varies within the group unless that column also defines the group. Doris rejects an ambiguous projection such as choosing event_time while grouping only by region.

ANY_VALUE(expression) explicitly requests any non-NULL representative value when one exists; it returns NULL when all candidates are NULL. This is meaningful when the exact selected value cannot change the business meaning. For example, after a predicate restricts every input row to one calendar date, any event date within each region is that same date. It is not meaningful when the group spans several dates and the result is supposed to identify one of them. In that case, add the date to the grain or state a rule such as MIN or MAX that has an interpretable meaning.

Never depend on which eligible row ANY_VALUE happens to select. Its purpose is to express indifference, not to provide a shortcut to a predictable first or latest value. See ANY_VALUE.

Introduce a Combinator only for its specialized behavior

A Combinator modifies how an Aggregate Function is represented or applied. State and merge forms support workflows that store or combine intermediate aggregate states. A FOREACH form applies an aggregate independently to corresponding positions of array values across rows.

These are useful when the data product genuinely contains mergeable aggregate states or positionally meaningful arrays. They are unnecessary for an ordinary revenue total. Prefer the direct and readable SUM(revenue) until the specialized requirement exists. See STATE, MERGE, and FOREACH for supported combinations and type constraints.

5.5 Use a Table Function to Expand Values into Rows

An event may store several tags, map entries, or delimited values in one row. When the analysis needs one of those elements per result row, use a Table Function to expand the value while retaining its relationship to the source row.

Expand a value belonging to each input row

Suppose an event has an array of tags:

Using EXPLODE(tags) with LATERAL VIEW associates every generated tag with its source event:

The result grain is now one row per event-tag occurrence, not one row per event. Summing event revenue after this expansion would count event 41 twice unless the metric intentionally measures tag attribution or the query first returns to an appropriate grain. Empty and NULL collections also require an explicit retention decision; consult the chosen Table Function's contract.

Table Functions suit row-dependent parsing or expansion. Examples include array expansion and splitting a delimited value. Doris commonly uses them with LATERAL VIEW. See EXPLODE; the documentation lists the other Table Functions beside it.

Table Function and TVF are different categories. A Table Function such as EXPLODE(tags) runs against each current input row and produces zero to many rows associated with that row. A Table-Valued Function (TVF) such as NUMBERS(...) or S3(...) is itself a relation source in FROM; it does not need a row from events_modelled to generate its relation. Module 3 already used the S3 TVF to expose external files as a temporary relation. Lab 5 uses NUMBERS("number" = "4") only as a small, self-contained contrast and does not revisit S3 access or persistence. See NUMBERS and S3.

5.6 Use CTEs to Express the Stages of a Complex Analysis

A Common Table Expression gives a temporary result set a name within one SQL statement. It can make the grain and responsibility of each stage visible. It does not create a permanent table.

For the daily-region scenario, divide the work as follows:

The structural outline is easier to review than one query layer that attempts every operation at once:

WITH filtered AS (
    -- select the input population and derive event_date
),
daily_region AS (
    -- establish the daily-region grain and calculate measures
),
scored AS (
    -- retain those rows and add window calculations
)
SELECT ...
FROM scored
ORDER BY ...;

This organization makes errors easier to locate. If purchase revenue is wrong, inspect the input predicate and conditional aggregate. If the row count is not three dates multiplied by eight regions, inspect the daily_region grouping. If “previous” crosses from one region to another, inspect the window partition.

A CTE is a semantic and readability boundary. Its presence does not guarantee that Doris materializes the result, executes each named block separately, or makes the query faster. Use EXPLAIN and a Query Profile when physical execution matters; do not infer a performance property from the syntax alone. See Common Table Expressions.

Preserve missing-period meaning

Grouping event detail produces rows only for groups represented by qualifying input. If a region has no selected event on a date, an event-only aggregation does not automatically invent a zero-valued row for that combination.

This matters to later windows. LAG reads the previous result row in its ordered window, which may not be the previous calendar day. A report that must show every calendar date needs a calendar relation and an outer Join or another explicit gap-filling design. Zero activity and an absent result row are different states.

5.7 Use Window Functions to Compare Rows Without Losing Their Grain

After aggregation, each row already represents a date and region. Window functions compare those rows while preserving them. Three clauses establish the comparison contract:

The same daily-region rows can participate in different window populations without changing their grain.

Compare each region with its previous observed row

For regional revenue trends, the independent sequence is each region:

LAG(purchase_revenue, 1, 0) OVER (
    PARTITION BY region
    ORDER BY event_date
)

The offset 1 means one preceding result row, and 0 is the default when no such row exists. The first displayed date therefore receives zero in the lab. That value does not claim that revenue before the selected range was zero. It only makes the boundary behavior explicit.

If dates are missing, LAG still reads the preceding row. Describe it as “previous observed date” unless the query has guaranteed a consecutive calendar relation.

State the cumulative frame

A running total for each region can use:

SUM(purchase_revenue) OVER (
    PARTITION BY region
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

The ROWS frame starts with the first selected row in that region and ends at the current row. Filtering earlier dates out of the input also removes them from the cumulative total. “Cumulative” must always name its starting scope.

A frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW contains at most three row positions. It represents three calendar days only when the input has one row for each consecutive date.

Choose the intended tie behavior

ROW_NUMBER assigns distinct sequential positions. Provide a tie-breaker when the position must be deterministic. RANK gives equal ordering values the same rank and leaves a gap after a tie:

Choose ROW_NUMBER when the output needs one deterministic position for each row. Choose RANK when equal metric values should share a business rank. For the module scenario, PARTITION BY event_date restarts the competition each day, while ORDER BY purchase_revenue DESC gives the largest value rank 1.

Window ordering and final result ordering serve different purposes. A window may define a revenue rank while the final ORDER BY displays date, rank, and region. The display order does not recalculate the window values. See Analytic (Window) Functions.

5.8 Choose the Right Extension for Missing Reusable Logic

Start with Doris built-in functions and clear SQL expressions. They already cover the date transformations, grouping, conditional metrics, expansion, and windows used in this module. Repeated expression syntax alone does not justify deploying user code.

Compare reuse and extension choices

In ARRAY_MAP(x -> x * 2, [1, 2, 3, 4]), the Lambda expression defines what happens to each array element. ARRAY_FILTER uses a Lambda predicate to decide which elements remain in the returned array. Neither expression registers a new function for other queries, and filtering array elements does not filter the surrounding table rows.

An Alias Function gives a reusable name to an expression composed from supported functions. It remains expression reuse. It does not provide a place to hide an external proprietary algorithm. See Alias Function.

Match the user-defined family to its row behavior

When the missing logic requires an implementation artifact, choose the family that matches its input and output contract:

A Java scalar UDF illustrates the deployment boundary:

implement the Java method and its type contract
        |
        v
package the implementation in a Java Archive (JAR)
        |
        v
make the artifact available to the required Doris nodes
        |
        v
register the SQL signature and implementation with CREATE FUNCTION
        |
        v
invoke the registered function in a query

CREATE FUNCTION registers metadata that points to the implementation; the statement does not contain the algorithm. The contract includes argument and return types, null behavior, implementation location, privileges, and function properties. A function declared immutable must return the same result for the same inputs. Logic that reads changing external state does not satisfy that promise.

The Backend (BE) executes the registered implementation as part of query execution. Deployment therefore introduces artifact availability, security, resource use, failure handling, compatibility, and upgrade responsibilities. Choose an extension because the behavior is both missing and reusable, not because the built-in catalog is unfamiliar.

Doris 4.1.3 documentation marks Python UDF, UDAF, and UDTF support as experimental. The lab uses the documented Java lifecycle as a non-executing template and does not build a JAR or register a function. Consult Java UDF, UDAF, UDWF, and UDTF, Python UDF family, and CREATE FUNCTION for the current implementation contracts.

Lab 5: Explore Doris Functions and Build Analytical Queries

Open Lab 5 — Explore Doris Functions and Build Analytical Queries after completing Lab 4, and plan about 30 minutes. It uses the persistent doris_course.events_modelled table containing 10,158,080 event rows. It reads that model without replacing it so Module 6 can enrich the same events.

The lab turns the choices in this module into observable results:

In Step 9, follow region_02 across the three dates. Its March 2 row reads the March 1 revenue through LAG, adds March 2 revenue to its regional cumulative total, and receives rank 1 within the March 2 partition. These values are direct query results. The lab does not claim that its CTEs were materialized or that one query form is faster.

When you change the guided builder, state two things before reading its values: what one row represents and which events contribute to the chosen metric. A different date grain, event filter, or measure produces a different business answer even when every query runs successfully.

The Lab ends with a practice: find the region that grew most from one day to the next, in stages with LAG and RANK. Predict the result first, then write the code in the empty cell; the collapsed reference solution after it also runs with Run All and leaves the Lab's tables as they were.

Summary

• Scalar functions derive one value per row, aggregate functions summarize a group, window functions add a value to every retained row, a Table Function expands each input row into zero or more rows, and a TVF supplies a relation to FROM; an AI Function also depends on an external model.
• Each measure has its own population: conditional aggregates let all events and purchases share one daily-region row. WHERE removes input rows before grouping, and HAVING removes groups by their aggregate values.
• ANY_VALUE returns any value of the group, so use it only when every candidate means the same thing, as the date does after a one-day filter; for "latest" or "earliest", use MAX or MIN.
• A CTE names one stage of a query, such as the filtered events, the daily-region groups, and the scored rows; it does not promise materialization or speed, and an event-only aggregation creates no row for a date without events.
• PARTITION BY sets each window's population, its ORDER BY defines "previous" and "cumulative", the frame decides which rows a running total includes, and ROW_NUMBER and RANK differ only when values tie.
• Start with built-in functions and SQL expressions; a Lambda passes element logic to a higher-order function, an Alias Function names an existing expression, and a UDF, UDAF, UDWF, or UDTF brings in external code with its deployment cost.

Quiz 5: Analyzing Data in Apache Doris

Open Quiz 5: Analyzing Data in Apache Doris. It has six single-choice questions and needs no running environment.

Next module: Module 6: Joining Data in Apache Doris.

References

• Function families: the combinators STATE, MERGE, and FOREACH, and the AI Functions overview. SHOW FUNCTIONS lists a database's own functions, and SHOW BUILTIN FUNCTIONS the built-in ones.
• Row-producing functions: the Table Function EXPLODE and the TVFs NUMBERS and S3.
• Analytical query structure: SELECT, Common Table Expressions, and Analytic (Window) Functions.
• Grouping and higher-order behavior: ANY_VALUE, ARRAY_MAP, and ARRAY_FILTER.
• Reusable extensions: Alias Function, Java UDF, UDAF, UDWF, and UDTF, Python UDF family, and CREATE FUNCTION.
• Result delivery: SELECT INTO OUTFILE.