***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** This Confluence article was automatically generated from Asciidoc. Any changes you make to this document will be overridden! If you want to change the content, consider leaving a comment. You can edit the content directly here: https://gitlab.pricefx.eu/training/pricefx-knowledge-base/-/tree/dev/public/content/docs/quick-reference/data-queries/analytics ***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING **********************

Analytics data should be stored in a different relational database than all other types of data. Queries to this database are performed with the DatamartContext API.

Types of tables that can be queried with the DatamartContext API:

• Datamarts

• Data Sources

• Data Feeds

Collection of Rows

The simplest form of a query is a non-aggregating query. Be careful about the number of rows your query returns – it might be very large.

Transactions.groovy

void forEachTransaction(Closure callback){
    def dataMartAPI = api.getDatamartContext()
    def table = dataMartAPI.getDatamart('Transaction')
    def query = dataMartAPI.newQuery(table, false)
            .selectAll()
    def results = dataMartAPI.streamQuery(query)

    // StreamResults is not iterable. Iterate in this non-standard way.
    while(results.next()){      // ❶
        def row = results.get()
        callback.call(row)      // ❷
    }

    results.close()             // ❸
}

❶ Iterate the table rows. Since the result is neither a collection nor an iterator, use for/while loop.
❷ Do something for each row.
❸ Close the stream after usage.

Where Filters

Limit the rows that are included in your results by applying an SQL WHERE clause. The rows that are included in the query must satisfy the filter that is provided to the where() method.

ExchangeRates.groovy

BigDecimal getExchangeRate(Date validFrom, String currencyFrom, String currencyTo){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDataSource('ccy')

    def filters = [
            Filter.lessOrEqual('CcyValidFrom', validFrom),
            Filter.greaterThan('CcyValidTo', validFrom),
            Filter.equal('CcyFrom', currencyFrom),
            Filter.equal('CcyTo', currencyTo),
    ]

    def query = dataMartAPI.newQuery(table, true)
            .select('CcyExchangeRate', 'ExchangeRate')
            .where(*filters)    // ❶

    def result = dataMartAPI.executeQuery(query)
    return result.getData().getAt(0)?.ExchangeRate
}

❶ Filter the rows with a WHERE filter.

Aggregation

More often than not, queries to Datamarts perform some sort of aggregation. Turn the query into an aggregation query by setting the second argument to newQuery() to true. Now you can wrap the fields that you want with an SQL aggregate function.

Transactions.groovy

BigDecimal getTotalSales(Date dateFrom, Date dateTo){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart('Transaction')

    def dateFilters = [
            Filter.greaterOrEqual('InvoiceDate', dateFrom),
            Filter.lessThan('InvoiceDate', dateTo),
    ]

    def query = dataMartAPI.newQuery(table, true)
            .select('SUM(InvoicePrice)', 'TotalSales') // ❶
            .where(*dateFilters)

    def result = dataMartAPI.executeQuery(query)
    return result.getData().getAt(0).TotalSales
}

❶ Aggregate the InvoicePrice field values. The use of an alias means that the result Map will have the key 'TotalSales', instead of InvoicePrice.

Grouping

Rows can can be grouped together according to the value of some field(s). This will automatically happen when the column in the select statement is not wrapped within an aggregate function.

Transactions.groovy

List<Map<String, Object>> getTotalSalesByYear(){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart("Transaction")

    def query = dataMartAPI.newQuery(table, true)
            .select('InvoiceDateYear')                  // ❶
            .select('SUM(InvoicePrice)', 'TotalSales')  // ❷
            .orderBy('InvoiceDateYear')

    def result = dataMartAPI.executeQuery(query)
    return result.getData().toList()
}

❶ Group by the InvoiceDateYear field values. This field is of the type dimension.
❷ Aggregate the InvoiceDateYear field values. This field is not of the type dimension.

Filtering

After performing an aggregation, you can filter the query results with a SQL HAVING clause. Apply it with the having() method on the query builder.

Transactions.groovy

List<Map<String, Object>> getTotalSalesByYear(BigDecimal totalSalesThreshold){
    def dataMartAPI = api.getDatamartContext()

    def table = dataMartAPI.getDatamart("Transaction")

    def resultFilters = [Filter.greaterOrEqual('TotalSales', totalSalesThreshold)]
    def query = dataMartAPI.newQuery(table, true)
            .select('InvoiceDateYear')
            .select('SUM(InvoicePrice)', 'TotalSales')
            .orderBy('InvoiceDateYear')
            .having(*resultFilters) // ❶

    def result = dataMartAPI.executeQuery(query)
    return result.getData().toList()
}

❶ Apply a filter to the query results.

Distinct Values

To retrieve only unique values from a Data Source, use the selectDistinct() method.

DataMart.groovy

List getUniqueValues(
        String dataMartName,
        String dimensionColumnName
) {
        def dataSourceAPI = api.getDatamartContext()
        def table = dataSourceAPI.getDatamart(dataMartName)
        def query = dataSourceAPI.newQuery(table, true)
                .select(dimensionColumnName)
                .selectDistinct() // ❶

        def results = dataSourceAPI.executeQuery(query)
        return results.getData().toList()[dimensionColumnName]
}

❶ selectDistinct() ensures that all retrieved values are unique.