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 by 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. To set up DM Query to use the Household sample data set, see the second example in
Understanding hierarchical data.
Example 1: Gender by Region at the Person level
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)
Here is the result set:
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.
Example 2: Gender by Region at the HDATA level
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)
Here is the result set:
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.
Gender has been up-leved using SUM. When used on categorical data, SUM takes the union.
Example 3: Age (auto-banded) by Gender at the Person Level
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.)
Here is the result set:
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
In this example, the results show the number of people.
Example 4: Age (auto-banded) by Gender at the HDATA Level
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)
This query is designed to be run using the connection option to return category values.
Here is the result set:
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
In this example, the results show the number of households.
Example 5: Gender by Purpose at the Trip Level
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)
Here is the result set:
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.
Example 6: Gender by Purpose at the Person Level
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)
Here is the result set:
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
In this example, the results show the number of people who took overseas trips.
Example 7: Region by Purpose at the HDATA Level
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)
Here is the result set:
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.
See also
