SlicingDice Documentation

SlicingDice - The All-in-One Solution Documentation

Welcome to the SlicingDice documentation. You'll find comprehensive guides and documentation to help you start working with SlicingDice as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    Guides

7.4 Query examples

On this page, you can find many query examples using SQL.

SQL-based guide

This guide follows an approach with SQL. If you want to see query examples via API, see the Query using the API guide.

SELECT

With this query, you'll retrieve all the data from your dimension - dimension - Dimension is a concept used in SlicingDice to describe a way to group columns for a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. On SlicingDice every database has at least one dimension (created by default), that is similar to the concept of a table, on relational databases. Dimensions are normally used to insert the same kind of data you inserted on another dimension, but associating it for a different type of entity. But it's also possible to use a dimension like a table on a relational database, where you normally create tables and build relations between them.
Let's say that you want to retrieve all users names and emails from your dimension - dimension - Dimension is a concept used in SlicingDice to describe a way to group columns for a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. On SlicingDice every database has at least one dimension (created by default), that is similar to the concept of a table, on relational databases. Dimensions are normally used to insert the same kind of data you inserted on another dimension, but associating it for a different type of entity. But it's also possible to use a dimension like a table on a relational database, where you normally create tables and build relations between them. user.
Check how to do it in the example below.

SELECT
  name,
  email
FROM
  user;

Notice that only the data that is stored in name and email columns will be retrieved from dimension - dimension - Dimension is a concept used in SlicingDice to describe a way to group columns for a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. On SlicingDice every database has at least one dimension (created by default), that is similar to the concept of a table, on relational databases. Dimensions are normally used to insert the same kind of data you inserted on another dimension, but associating it for a different type of entity. But it's also possible to use a dimension like a table on a relational database, where you normally create tables and build relations between them. user.

You can also make a generalist query, returning all data from the user dimension - dimension - Dimension is a concept used in SlicingDice to describe a way to group columns for a database. Each database can contain multiple dimensions and each dimension can contain multiple columns. On SlicingDice every database has at least one dimension (created by default), that is similar to the concept of a table, on relational databases. Dimensions are normally used to insert the same kind of data you inserted on another dimension, but associating it for a different type of entity. But it's also possible to use a dimension like a table on a relational database, where you normally create tables and build relations between them. , like the following example.

SELECT *
FROM user;

What is the "score" column?

SELECT * queries will show an auto-generated score column. The score column serves as a counter that increments itself for each WHEREcondition clause matched.
If the query doesn't have a WHERE clause, the score will always be 1.

Extraction limitation

SELECT * queries that don't have a WHERE clause are limited to only return a maximum of 1,000 rows in SlicingDice workbench.


SELECT on event columns

With these queries, you can retrieve data from event columns - event columns - This column type is used to store event data that is associated to a date/time. For instance you can track a click on an Add to Cart button in your Click event column by storing the value Add to Cart and the date/time of the event YYYY-MM-DDT00:00:00Z. .
Let's say that you want to retrieve only the cities and states that sold products between 2019-01-08 and 2019-01-22.
To answer this question you can execute the query below.

SELECT
  city, state
FROM
  sales
WHERE [purchases-values.date] BETWEEN '2019-01-08T00:00:00Z'
  AND '2019-01-22T00:00:00Z';

COUNT

The COUNT query will retrieve only the number of results that you have in a particular dimension that satisfy the query conditions.
Let's say that you want to know how many rows your default database has stored.
You can answer this question by doing the following query.

SELECT COUNT(*)
FROM default

The result of this query will be a number.


COUNT DISTINCT

The COUNT query will retrieve only the number of distinct results that you have in a particular dimension that satisfy the query conditions.
Let's say that you want to know the number of different suppliers' countries. If 3 different suppliers are from USA, this will only be counted once.

SELECT COUNT (DISTINCT country)
FROM supplier

COUNT in Event Columns

The COUNT operator can also be used with Event Columns - Event Columns - This column type is used to store event data that is associated to a date/time. For instance you can track a click on an Add to Cart button in your Click event column by storing the value Add to Cart and the date/time of the event YYYY-MM-DDT00:00:00Z. .
The only difference of the COUNT operator in event columns is the mandatory use of the BETWEEN operator when comparing date intervals.
In the following example, we're asking how many users accessed the home-page between 2019-01-08 and 2019-01-22

SELECT
  COUNT(*)
FROM
  users
WHERE
  [actions.value] = 'home-page'
  AND [actions.date] BETWEEN '2019-01-08T00:00:00Z'
  AND '2019-01-22T00:00:00Z';

Brackets needed

When non-alphanumeric characters exist in a column name, you need to add brackets before and after its name. Most notable uses of brackets are the following:

  • When you query the entity-id column: [entity-id];
  • When you query event columns - event columns - This column type is used to store event data that is associated to a date/time. For instance you can track a click on an Add to Cart button in your Click event column by storing the value Add to Cart and the date/time of the event YYYY-MM-DDT00:00:00Z.

Aggregation in attribute columns

An aggregation query is a type of query that group the result-set by one or more columns, often using metrics.
Metrics are functions that calculate the values of the result-set of a given column.
Below you can find all the metrics that can be used.

Metric
Purpose

AVG(column)

Return the average column value of the result-set.

MIN(column)

Return the lowest column value of the result-set.

MAX(column)

Return the highest column value of the result-set.

SUM(column)

Return the sum of the column values of the result-set.

Let's see an example of how to use aggregation metrics.
Let's suppose that you want to know the average, minimum and maximum ages of users, grouped by the operating system and web browser.
You can find the answer executing a query like the following.

SELECT
  os,
  browser,
  AVG(age),
  MIN(age),
  MAX(age)
FROM
  users
GROUP BY
  os,
  browser;

Aggregation in event columns

An aggregation query is a type of query that group the result-set by one or more columns, often using metrics.
Metrics are functions that calculate the values of the result-set of a given column.
Below you can find all the metrics that can be used.

Metric
Purpose

AVG(column)

Return the average column value of the result-set.

MIN(column)

Return the lowest column value of the result-set.

MAX(column)

Return the highest column value of the result-set.

SUM(column)

Return the sum of the column values of the result-set.

The only difference of aggregation queries in event columns is the mandatory use of the BETWEEN operator if comparing interval of dates.
As an example, let's suppose that you want to know the highest value of purchase-values each day, based on a 10 days interval.
You can find the answer executing a query like the following.

SELECT
  MAX([purchase-values.value])
FROM
  users
GROUP BY
  INTERVAL([purchase-values.value]) = 'days'
HAVING
  [purchase-values.date] BETWEEN '2019-01-13T00:00:00Z'
  AND '2019-01-22T00:00:00Z';

Brackets needed

When non-alphanumeric characters exist in a column name, you need to add brackets before and after. Most notable uses of the brackets are the following:

  • When you query the entity-id column;
  • When you query event columns - event columns - This column type is used to store event data that is associated to a date/time. For instance you can track a click on an Add to Cart button in your Click event column by storing the value Add to Cart and the date/time of the event YYYY-MM-DDT00:00:00Z.