4 ms·
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ev
by sigwinch28 1y ago
Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)
- valiant55 1y agoIt depends when you see it, but I agree that DISTINCT shouldn't be used in production. If I'm writing a one off query and DISTINCT gets me over the finish line sparing me a few minutes then that's fine.
- viraptor 1y agoWhich categories did the user post in? Which projects did the user interact with in the last week? That's all normal DISTINCT usage.
- ndsipa_pomu 1y agoThere's nothing wrong with using DISTINCT correctly and it does belong in production. The author is complaining about developers that just put in DISTINCT as a matter of course rather than using it appropriately.
- echelon 1y agoDISTINCT, as well as the other aggregation functions, are fantastic for offline analytics queries. I find a lot of use for them in reporting, non-production code.
- sgarland 1y agoBecause a city/region/state can be uniquely identified with a postal code (hell, in Ireland, the entire address is encapsulated in the postal code), but the reverse is not true. At scale, repeated low-cardinality columns matter a great deal.
- bdangubic 1y agosaying zipcodes uniquely identify city/state/region is like saying John uniquely identifies a human :)
- virissimo 1y agoThere are ZIP codes that overlap a city and also an unincorporated area. Furthermore, there are zip codes that overlap different states. A data model that renders these unrepresentable may come back to bite you.
- pbnjay 1y agoFYI this is not true in the US. Zip codes identify postal routes not locations
- lucyjojo 1y agothese kinds of things are almost never true in the real world.
- sgarland 1y agoEDIT: TIL that there are cross-state ZIP codes.
- Breza 11mo agoThis assumption got me in trouble as a junior analyst years ago. I was asked to analyze our customer base and wrote something like the below. Management congratulated me on finding thousands more customers than we'd ever had before. SELECT zipcode.rural_urban_code, COUNT(*) AS n_customer FROM customer INNER JOIN zipcode USING(zipcode) GROUP BY 1;
- ndsipa_pomu 1y agoOne reason to have excessively normalised tables would be to ensure consistency so that you don't have to worry about various records with "London", "LONDON", "lindon" etc.