3 ms·
I just keep getting faster at SQL. It's fun. I used CROSS LATERAL JOIN last night to find the nearest regions to points. Super fast and easy: https://carto.com/
by ideamotor 5y ago
I just keep getting faster at SQL. It's fun. I used CROSS LATERAL JOIN last night to find the nearest regions to points. Super fast and easy: https://carto.com/blog/lateral-joins/ https://carto.com/blog/lateral-joins/. Right on the data. SQL does change over time, as Postgres adds features, but especially when you use MADlib, Postgis, and other extensions. I mean ... https://postgis.net/docs/reference.html https://postgis.net/docs/reference.html. And everything I learned when I started is still relevant. It's my go-to crunch tool for data processing.
- ideamotor 5y agoI just want to throw out there, one of the weaknesses I've seen is the lack of fast options for pivoting (wide <-> long). I've used the tablefunc module: https://www.postgresql.org/docs/13/tablefunc.html https://www.postgresql.org/docs/13/tablefunc.html but it's not particularly fast in my experience (maybe they've improved it since it has been awhile) and the syntax is a bit odd. I'm spoiled with R (newer version) tidyr's pivot_wider and pivot_longer. Gosh, yes it's been awhile. I posted this, ahem, over 8 years ago: https://stackoverflow.com/questions/15415446/pivot-on-multiple-columns-using-tablefunc https://stackoverflow.com/questions/15415446/pivot-on-multip.... Guess what, while to many things change, I still use SQL. I should, uh, try it again. Or try PL/R, PL/Python, or PL/pgsql. There are options.