![]() If I wanted to debug this, I can easily change the final select to select from each CTE in turn, validating before I move onto the next one. One way to achieve this (without CTEs) is the following: select s.name as station_name, st_distance ( st_geogpoint( s.longitude, s.latitude), st_geogpoint(-0.118092, 51.509865) ) as distance_from_city_centre_m, count(j.rental_id) as journey_count from `bigquery-public-data.london_bicycles.cycle_stations` s inner join `bigquery-public-data.london_bicycles.cycle_hire` j on j.end_station_id = s.id and cast(j.end_date as date) >= date_sub('', interval 1 year) group by station_name, s.longitude, s.latitude having count(j.rental_id) > ( select journey_count from ( select dense_rank() over (order by count(j.rental_id) desc) as rank, s.id as station_id, count(j.rental_id) as journey_count from `bigquery-public-data.london_bicycles.cycle_stations` s inner join `bigquery-public-data.london_bicycles.cycle_hire` j on j.end_station_id = s.id and cast(j.end_date as date) >= date_sub('', interval 1 year) where j.end_station_id in ( select s.id as station_id from `bigquery-public-data.london_bicycles.cycle_stations` s where st_distance( st_geogpoint( s.longitude, s.latitude), st_geogpoint(-0.118092, 51.509865) ) = date_sub('', interval 1 year) group by s.station_id, s.station_name, s.distance_from_city_centre_m ), stations_near_centre as ( select sp.station_id, sj.journey_count, dense_rank() over (order by sj.journey_count desc) journey_rank, from station_proximity sp inner join station_journeys sj on sj.station_id = sp.station_id where sp.distance_from_city_centre_m ( select journey_count from stations_near_centre s where s.journey_rank = 1 ) order by journey_count desc I then want to extract all other stations that have seen more journeys than this and analyse their distance from the city centre. ![]() ![]() In this example, I want to further explore the stations within 500m of the city centre, and single out the station that has seen the most journeys terminate there over the past 12 months. Easier to maintain changing logic can now be done in a more modulised way, and in a single place.Easier to debug you build your SQL in stages, testing the output of each CTE before continuing to the next.One way we can use CTEs to achieve this in our SQL code is to use CTEs as a way of breaking up complex SQL queries into smaller, bite-size chunks. And, I think this much easier to read than the first query too - what do you think?Īnd because my stations CTE is temporary, I haven’t had to clutter my database with any materialised (permanent) tables, or views.Ĭycle hire stations, within 500m of the center of London #2 Simplify complex SQL (KISS)Īs in other areas of programming, and life for that matter, it really helps if you try and keep things simple where you can. Here is where a CTE comes to the rescue: with stations as ( select name as station_name, st_distance ( st_geogpoint( longitude, latitude), st_geogpoint(-0.118092, 51.509865) ) as distance_from_city_centre_m from `bigquery-public-data.london_bicycles.cycle_stations` ) select station_name, distance_from_city_centre_m from stations where distance_from_city_centre_m <= 500 order by distance_from_city_centre_m This is something we want to avoid logic should be defined once, making it easier to read and modify later on if required. Whilst this is perfectly functional and returns the correct result (below in case you were curious!), you will note the logic in bold to calc the distance is duplicated. ![]() In this query, I want to find all cycle stations that are within 500m of London’s city centre, and return the station name and the distance from the centre: select name as station_name, st_distance ( st_geogpoint( longitude, latitude), st_geogpoint(-0.118092, 51.509865) ) as distance_from_city_centre_m from `bigquery-public-data.london_bicycles.cycle_stations` where st_distance ( st_geogpoint( longitude, latitude), st_geogpoint(-0.118092, 51.509865) ) <= 500 order by distance_from_city_centre_m #1 Avoid duplicate column transformations
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |