Advanced hierarchical SQL queries

This topic supplements Hierarchical SQL queries and Advanced SQL queries with some examples of advanced SQL queries on the HDATA hierarchical view of the data. The examples are based on the UNICOM Intelligence Data File version of the Household sample data set. The following diagram shows the levels structure in the Household sample.

Each case at the top level corresponds to a household. For more information about the household sample, including details of the questionnaire logic and the variables at each level, see The Household sample.

You can run the example queries shown below using the DM Query sample tool, which is supplied with the UNICOM Intelligence Developer Documentation Library. Except where stated otherwise, these examples were run using the connection option to return category names rather than category values. You select this option on the Advanced tab in the Data Link Properties dialog box. To set up DM Query to use the Household sample data set, see the second example in Understanding hierarchical data.

SELECT GroupBy.Col[0] As Gender,
COUNT(^.Region) As [Base],
SUM(^.Region={North}) As North,
SUM(^.Region={South}) As South,
SUM(^.Region={East}) As East,
SUM(^.Region={West}) As West
FROM HDATA.Person
WHERE Gender IS NOT NULL
GROUP BY Gender ON {Male,Female} WITH(BaseSummaryRow)

Gender Base North South East West
-------- ---- ----- ----- ---- ----
{} 25 4 11 7 3
{male} 12 1 6 4 1
{female} 13 3 5 3 2

In this example, the results show the number of cases at the Person level. Each case at the Person level corresponds to a person in a household.

SELECT GroupBy.Col[0] As Gender,
COUNT(Region) As [Base],
SUM(Region={North}) As North,
SUM(Region={South}) As South,
SUM(Region={East}) As East,
SUM(Region={West}) As West
FROM HDATA
WHERE SUM(Person.(Gender))>{}
GROUP BY SUM(Person.(Gender)) ON {Male,Female} WITH(BaseSummaryRow)

Gender Base North South East West
-------- ---- ----- ----- ---- ----
{} 10 2 3 3 2
{male} 8 1 3 3 1
{female} 8 1 3 2 2

In this example, the results show the number of cases at the HDATA level, which in the Household sample correspond to households.

SELECT GroupBy.Col[0] As Age,
COUNT(Gender) As [Base],
SUM(Gender={Male}) As Male,
SUM(Gender={Female}) As Female
FROM HDATA.Person
WHERE Age IS NOT NULL
GROUP BY Age.Band(0, 10, 10) ON {1,2,3,4,5,6,7,8,9,10}
WITH(BaseSummaryRow)

In the Household sample, Age is a numeric variable. This example uses the Band function to group the numeric age values into 10 age groups. Note that this query is designed to be run using the connection option to return category values. (This query is not suitable for running with the connection option to return category names, because the numeric category values returned by the Band function do not correspond to mapped category values.)

Age Base Male Female
---- ---- ---- ------
{} 25 12 13
{1} 6 4 2
{2} 2 1 1
{3} 5 2 3
{4} 6 3 3

{5} 3 1 2
{6} 0 0 0
{7} 1 0 1
{8} 1 0 1
{9} 1 1 0
{10} 0 0 0

SELECT GroupBy.Col[0] As Age,
SUM(COUNT(Person.(Gender))>0) As [Base],
SUM(SUM(Person.(Gender={Male}))>0) As Male,
SUM(SUM(Person.(Gender={Female}))>0) As Female
FROM HDATA
WHERE SUM(Person.(Age.Band(0, 10,10)))>{}
GROUP BY SUM(Person.(Age.Band(0, 10,10))) ON {1,2,3,4,5,6,7,8,9,10}
WITH(BaseSummaryRow)

Like the previous example, this query is designed to be run using the connection option to return category values.

Age Base Male Female
---- ---- ---- ------
{} 10 8 8
{1} 3 3 3
{2} 1 1 1
{3} 3 2 1
{4} 4 4 3
{5} 2 2 2
{6} 0 0 0
{7} 1 0 1
{8} 1 1 1
{9} 1 1 1
{10} 0 0 0

SELECT GroupBy.Col[0] As Gender,
COUNT(Purpose) As [Base],
SUM(Purpose>={business}) As Business,
SUM(Purpose>={vacation}) As Vacation,
SUM(Purpose>={education}) As Education,
SUM(Purpose>={visiting_relatives}) As [Visiting Relatives],
SUM(Purpose>={sport}) As Sport
FROM HDATA.Person.Trip
WHERE ^.Gender IS NOT NULL
GROUP BY ^.Gender ON {Male, Female}
WITH(BaseSummaryRow)

Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------------ -----
{} 24 6 15 1 5 2
{male} 11 0 9 0 3 2
{female} 13 6 6 1 2 0

In this example, the results show the number of cases at the Trip level. Each case at the Trip level corresponds to an overseas trip.

SELECT GroupBy.Col[0] As Gender,
SUM(COUNT(Trip.(Purpose))>0) As [Base],
SUM(SUM(Trip.(Purpose>={business}))>0) As Business,
SUM(SUM(Trip.(Purpose>={vacation}))>0) As Vacation,
SUM(SUM(Trip.(Purpose>={education}))>0) As Education,
SUM(SUM(Trip.(Purpose>={visiting_relatives}))>0) As [Visiting Relatives],
SUM(SUM(Trip.(Purpose>={sport}))>0) As Sport
FROM HDATA.Person
WHERE Gender IS NOT NULL
GROUP BY Gender ON {Male, Female}
WITH(BaseSummaryRow)

Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------
------ -----
{} 12 1 12 1 4 2
{male} 6 0 6 0 2 2
{female} 6 1 6 1 2 0

SELECT GroupBy.Col[0] AS Region,
SUM(SUM(Person.(SUM(trip.(purpose > {}))))>0) AS [Base],
SUM(SUM(Person.(SUM(trip.(purpose >= {business}))))>0) AS Business,
SUM(SUM(Person.(SUM(trip.(purpose >= {vacation}))))>0) AS Vacation,
SUM(SUM(Person.(SUM(trip.(purpose >= {education}))))>0) AS Education,
SUM(SUM(Person.(SUM(trip.(purpose >= {visiting_relatives}))))>0) AS "Visiting Relatives",
SUM(SUM(Person.(SUM(trip.(purpose >= {sport}))))>0) AS Sport
FROM hdata
GROUP BY Region ON {north, south, east, west} WITH (BaseSummaryRow)

Gender Base Business Vacation Education Visiting Relatives Sport
------- ---- -------- -------- --------- ------------------ -----
{} 12 1 12 1 4 2
{male} 6 0 6 0 2 2
{female} 6 1 6 1 2 0

The result set shows the number of households in each region that have taken at least one overseas trip. This query demonstrates up-leving data by two levels, that is, the data in the Trip-level Purpose variable has been up-leved twice to the top (HDATA) level.