You are here:
Use SQL to Create Insights
Write SQL to define calculated and streaming insights in Data 360. Each query must follow a specific structure and use supported functions, operators, and data types.
Required Editions
| Available in: All Editions supported by Data 360. See Data 360 edition availability. |
A calculated insight query follows this structure:
SELECT <attributes>, <aggregation(measures)>
FROM <data_model_object>
JOIN [INNER | LEFT | RIGHT | FULL] <data_model_object> -- optional
WHERE <row_filter> -- optional
GROUP BY <dimensions>- Measure
- A quantitative value produced by an aggregation function, such as a total amount or record count. Every query must include at least one measure.
- Dimension
- A qualitative field used to categorize and group measures, such as a product name, date,
or profile ID. Any field in the
SELECTclause that isn't inside an aggregation function is treated as a dimension and must also appear in theGROUP BYclause.
Note To include a calculated insight in a segment, add the segmented table as a
JOIN and include its primary key as a dimension in your query.For the full list of supported syntax rules, functions, and operators, see the reference topics in this section.
- Create a Calculated Insight Using SQL
Write SQL expressions to create your metrics and dimensions from mapped objects and fields. You can create a calculated insight in Data 360 using SQL. - Create a Streaming Insight Using SQL
Write SQL expressions to compute streaming metrics across dimensions from your near real-time data sources. A streaming insight analyzes data at specific points in time. Streaming insights help build time series aggregations in near real time to orchestrate and optimize your data within Data 360. - SQL Rules for Insights
When you write SQL for calculated or streaming insights in Data 360, rules govern how each clause is interpreted and what constraints apply. You can also embed a nested subquery in a WHERE or JOIN clause to restrict which rows the outer query returns. - Data Types for Insights SQL
When you define a calculated or streaming insight with SQL in Data 360, use the supported data types for your measures and dimensions. Measures are the numerical values you aggregate; dimensions are the qualitative fields you group by. - SQL Functions for Insights
Use these aggregate, datetime, analytical, formatting, math and statistical, and boolean functions when you author calculated insights in Data 360. The Aggregatable column indicates whether a function's output can be rolled up across fewer dimensions when queried in segments or activations. - SQL Operators for Insights
Use these arithmetic, currency, and logical operators in SQL expressions when you author calculated insights in Data 360. - Formulas, Operators, and Calculated Insights
Create audiences in different ways and at different stages of your workflow using formulas, operators, and calculated insights in Data 360. During ingestion, you can use formulas to perform operations on row-based data used downstream. For segmentation, audiences are created with segment operators. - SQL Use Cases for Insights Data 360
There are several useful code blocks when creating insights in Data 360.
Did this article solve your issue?
Let us know so we can improve!
