tDSQL4JSONを使用してデータを分析する

SELECT {
   countries = (
      // Iterate on the main input data collection.
      FROM data
	  // Expand each country locations into individual records.
      UNNEST locations
	  // Group location records by country name.
      GROUP BY country_name
	  // Name this group for reference in subsequent clauses.
      GROUP AS g_country_name
	  // Count the number of time zones per country with the use of a variable.
      LET $nbr_of_time_zones = count(country_name)
	  // Filter to keep only countries with more than one time zone.
      HAVING $nbr_of_time_zones > 1
	  // Sort results by number of time zones in descending order.
      ORDER BY $nbr_of_time_zones DESC
      SELECT {
         country_name,
         nbr_of_time_zones = $nbr_of_time_zones,
		 // Build a nested list of time zones for each country.
         time_zones = (
		    // Iterate over location records within each country group.
            FROM g_country_name
            SELECT {
               utc_offset,
			   // Build a nested list of cities for each time zone.
               main_cities = (
			      // Expand the city_list array, an implicit alias city_list is automatically defined and can be used to select items.
                  FROM city_list
                  SELECT city_list
               )
            }
         )
      }
   )
}