Complex Digest with Query Examples

This digester contains many more columns. It produces data suitable for generating statistical reports and it demonstrates most of the capabilities of digesters.

{
    "id": 1002,
    "method": "registerdigest",
    "params": {
        "name": "binoperationstasks",
        "filter": {
            "AND": [
                {
                    "key": "labela",
                    "EQ": "Bin Operations Task"
                },
                {
                    "key": "labelc",
                    "EQ": "binOperationsTask"
                }
            ]
        },
        "eventFilter": {
            "OR": [
                {
                    "KEYEXISTS": "taskDate"
                },
                {
                    "KEYEXISTS": "uprn"
                },
                {
                    "KEYEXISTS": "binTypes.quantity"
                }
            ]
        },
        "columns": [
            {
                "name": "Process",
                "type": "varchar",
                "size": 50,
                "operation": "label",
                "key": "labela"
            },
            {
                "name": "BusinessKey",
                "type": "varchar",
                "size": 50,
                "operation": "label",
                "key": "labelb"
            },
            {
                "name": "Sealed",
                "type": "boolean",
                "operation": "issealed"
            },
            {
                "name": "Started",
                "type": "datetime",
                "operation": "firsttimestamp"
            },
            {
                "name": "SlotType",
                "type": "varchar",
                "size": 16,
                "operation": "firstvalue",
                "key": "slotType"
            },
            {
                "name": "ProcessType",
                "type": "varchar",
                "size": 20,
                "operation": "firstvalue",
                "key": "processType"
            },
            {
                "name": "Ward",
                "type": "varchar",
                "size": 50,
                "operation": "firstvalue",
                "key": "loc_ward"
            },
            {
                "name": "Area",
                "type": "varchar",
                "size": 50,
                "operation": "firstvalue",
                "key": "loc_area"
            },
            {
                "name": "Latitude",
                "type": "float",
                "operation": "firstvalue",
                "key": "loc_lat"
            },
            {
                "name": "Longitude",
                "type": "float",
                "operation": "firstvalue",
                "key": "loc_lon"
            },
            {
                "name": "taskDate",
                "type": "varchar",
                "size": 20,
                "operation": "lastvalue",
                "key": "taskDate"
            },
            {
                "name": "UserRole",
                "type": "varchar",
                "size": 20,
                "operation": "firstvalue",
                "key": "userRole"
            },
            {
                "name": "BinTypes",
                "type": "table",
                "operation": "all",
                "eventFilter": {
                    "AND": [
                        {
                            "KEYEXISTS": "binTypes.quantity"
                        },
                        {
                            "key": "binTypes.reviewed",
                            "EQ": true
                        }
                    ]
                },
                "columns": [
                    {
                        "name": "Quantity",
                        "type": "int",
                        "operation": "value",
                        "key": "binTypes.quantity"
                    },
                    {
                        "name": "Charge",
                        "type": "float",
                        "operation": "value",
                        "key": "binTypes.charge"
                    },
                    {
                        "name": "BinType",
                        "type": "varchar",
                        "size": 50,
                        "operation": "value",
                        "key": "binTypes.binType"
                    },
                    {
                        "name": "ServiceChannel",
                        "type": "varchar",
                        "size": 20,
                        "operation": "value",
                        "key": "binTypes.serviceChannel"
                    },
                    {
                        "name": "WhyReplacing",
                        "type": "varchar",
                        "size": 50,
                        "operation": "value",
                        "key": "binTypes.whyReplacing"
                    }
                ]
            }
        ]
    }
}

The "eventFilter" means that in addition to the "filter" on labels, each history will only become eligible for digestion once it contains an event with either a "taskDate", "uprn" or "binTypes.quantity".

The columns "Process" and "BusinessKey" copy values from history labels just like in the first example.

The column "Sealed" copies the "sealedness" of the entire history. That is whether the history has been sealed at the time of digestion.

The column "Started" copies the timestamp of the first event that is considered in the history. The first event that is considered in each history has either a "taskDate", "uprn" or "binTypes.quantity". A column level "eventFilter" could also be set, which would bring even fewer events into consideration. This can be useful to get data like "when was this case first reviewed" or "when was this case finally closed".

Columns "SlotType", "ProcessType", "Ward", "Area", "Latitude", "Longitude", "taskDate", "UserRole" all use the "firstvalue" or "lastvalue" operations to copy fields from the history into the digest.

The column "BinTypes" is much more complicated and interesting. Because multiple bins are recorded one-per-event in the history we need to keep data from multiple events in the same history. Keeping just the first or last value would miss all the bins other than the first or last one. The operation is "all" and the column type is "table". The digest will create a child view called "dh_binoperationstasks_bintypes_vw" which is linked to the main digest "dh_binoperationstasks_vw" by the public history id. "dh_binoperationstasks_vw" will have one-row-per-eligible-history, containing the labels, fields and timestamps that we have already described. "dh_binoperationstasks_bintypes_vw" can have any number of rows associated with each one row in "dh_binoperationstasks_vw". "BinTypes" has its own event filter so that only events with "binTypes.quantity" and "binTypes.reviewed"=true get considered when populating the child view. "BinTypes" has 5 columns which all use the "value" operation to copy data from the event into the child view. The sub-operations available within "all" are "value" and "timestamp".

Query Techniques

All Data

-- This shows all the digested bin tasks data by joining both views
SELECT *
FROM dh_binoperationstasks_vw tasks, dh_binoperationstasks_bintypes_vw bintypes
WHERE tasks.PublicID = bintypes.PublicID
ORDER BY tasks.PublicID

Notice that some business keys (the first two rows) include multiple bin types and charges and the some fields (bottom right) contain NULL because there was no value in the history to digest.

Focused aggregate query

This query uses the aggregate function SUM() and groups the results by ward to answer the question "how many bins in each ward". This is a very useful type of query, but to produce a dashboard or statistical report you would have to issue many similar queries.

-- Focused aggregate query for "how many bins in each ward"
SELECT SUM(quantity) AS NumBins, ward
FROM dh_binoperationstasks_vw tasks, dh_binoperationstasks_bintypes_vw bintypes
WHERE tasks.PublicID = bintypes.PublicID
GROUP BY ward

Group by everything aggregate query

This is the form of query that most of our dashboards and reports have tended to use. All the columns of interest to the report appear either in an aggregate function or in the GROUP BY clause. While the result-set might not be as useful as a focused query it is simple to post-process it into multiple useful metrics. You could run through the result-set once, row by row, and maintain counters and totals for, say, the total price charged for new bins and the number of damaged bins in each ward.

-- This is the classic "group by everything" structure that our projects have tended to use
SELECT bintype, SUM(quantity) AS NumBins, SUM(quantity * charge) AS Gross, 
    slottype, ward, userrole, servicechannel, processtype, whyreplacing
FROM dh_binoperationstasks_vw tasks, dh_binoperationstasks_bintypes_vw bintypes
WHERE tasks.PublicID = bintypes.PublicID
GROUP BY bintype, slottype, ward, userrole, servicechannel, processtype, whyreplacing

Time buckets

Some reports need to split the data into different periods, but you don't want to go back to the database for every single period. This technique uses an inner query with a CASE statement to put the data into different time buckets. The outer query can group by all the usual fields as well as the time buckets.

-- Inner query with time buckets
SELECT start_bucket, bintype, SUM(quantity) AS NumBins, SUM(quantity * charge) AS Gross, 
slottype, ward, userrole, servicechannel, processtype, whyreplacing
FROM (
    SELECT
        (
            CASE WHEN started >= '2020-12-01 00:00:00' AND started < '2021-01-01 00:00:00' THEN 'December'
            WHEN started >= '2021-01-01 00:00:00' AND started < '2021-02-01 00:00:00' THEN 'January'
            WHEN started >= '2021-02-01 00:00:00' AND started < '2021-03-01 00:00:00' THEN 'February' END
        ) AS start_bucket,
        bintype, quantity, charge, slottype, ward, userrole, servicechannel, processtype, whyreplacing
    FROM dh_binoperationstasks_vw tasks, dh_binoperationstasks_bintypes_vw bintypes
    WHERE tasks.PublicID = bintypes.PublicID
) bucketedbins
GROUP BY start_bucket, bintype, slottype, ward, userrole, servicechannel, processtype, whyreplacing

This result-set would still be post-processed row-by-row to add up the counts and totals for different wards and different months.