Stockholm train systemname2/18/2024 The scheme should be opening this summer and is then due to expand by 50% by Autumn 2013. The initial map has 413 stands – the districts either side of Central Park are missing, as is central Brooklyn, as these areas are still undergoing consultation and will gain coverage next year. I’ve extracted the data from the official map and added it to my own set of maps for 50+ bike share cities across the world allowing for a direct comparison between the scheme and the existing ones. The technology used is the same and the bikes are also the same design. NYC’s scheme is clearly influenced by London’s – its of a similar size, it has a big sponsor from financial services and a mayor fully behind it, and a Boris Bike from London even appears on the front cover of the NYC DOT presentation to communities. In several other cities around the world, their bike share schemes are known as City Bikes, such as Stockholm and Vienna, so Citi Bike has a good chance of becoming the “on the street” name for the scheme, unlike the unwieldy “Barclays Cycle Hire” name we have here in London – most people here know them as the snappier, if politically incorrect “Boris Bikes”. New York City last week released a preliminary map showing the proposed sites for the launch of its bike sharing scheme, now named Citi Bike (with Citigroup being the lead sponsor along with Mastercard).Ĭitigroup’s sponsorship is crucial for the scheme, which has promised no public subsidy on at least operating costs, and is a rather convenient sponsor in terms of its name. (select date(b1.timestamp) theday1, b1.timestamp, b1.bikes from bike_agg_london b1 inner join (select date(timestamp) as theday2, min(bikes) as min_bikes from bike_agg_london group by date(timestamp)) b2 on (date(b1.timestamp) = b2.theday2 and b1.bikes = b2.min_bikes)) b3 group by theday1, bikes date Select theday1, time(min(timestamp)), bikes from The fix is to use an additional wrapper (tables co3) to eliminate these duplicate rows: Using distinct won’t get rid of these, because the time(timestamp) does vary. There is one problem, where if there are multiple rows in a day that share the same min(bikes) value, they each appear. It’s the second solution from the above link. Select date(b1.timestamp) theday1, b1.timestamp, b1.bikes from bike_agg_london b1 inner join (select date(timestamp) as theday2, min(bikes) as min_bikes from bike_agg_london group by date(timestamp)) b2 on (date(b1.timestamp) = b2.theday2 and b1.bikes = b2.min_bikes) date Here’s the SQL that worked for me, quite quickly (~18 seconds for around 166000 rows representing 600 days): Here’s a technique that worked for me (the second solution) Here’s 10 ways to solve the problem, although I tried a few and they didn’t work for me. I don’t want to aggregate the time(timestamp) though – I want the value associated with the minimum bikes, rather than the maximum, minimum or average (etc) value. This is because the time(timestamp) is not part of the “group by” (aggregate) clause, and all fields in a query must be included in the group by unless they are part of the aggregate. This produces the minimum bikes number for each day, which is great, but the timestamp included is just the first one of each day (in fact it could be a randomly chosen timestamp from within the day, but MySQL’s internal logic happens to pick the first one out). Select date(timestamp), time(timestamp), min(bikes) from bike_agg_london group by date(timestamp) date The question: What is the time each day that we a see a minimum of available bikes for? (a research question – as finding this answer will tell us something about the commuting habits of the city.) This is the name for the type of query that I was trying, although in fact I am trying to find a set of minimum (rather than maximum) values. There is a surprisingly difficult task to solve with MySQL queries, which I’ve been spending some time trying to do – the Groupwise Maximum problem.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |