Practical SQL for Data Analysis

Within the VA hospital system, the data admins often got onto their "soapboxes" to dress down the 1400+ data analysts for writing inefficient SQL queries. If you need 6 million patients, joining data from 15 tables, gathering 250 variables, a beginning SQL user has the potential to take 15-20 hours where they could be pulling for 1-2 if they do some up-front filtering and sub-grouping within SQL. If you already know you'll throw out NA's on certain variables, if you need a certain date or age range, or even the way you format these filters: these all lead to important savings. And this saves R from being full on memory, which would often happen if you fed it way too much data.

Within a system of 1400 analysts, it makes a big difference if everyone's taking 2X or 4X the time pulling that they could be. Then, even the efficiently written pulls get slowed down, so you have to run things overnight...and what if you had an error on that overnight pull? Suffice to say, it'd have been much simpler if people wrote solid SQL from the start.

> it's that people way overuse it for SQL tasks as this article points out

I'm very confused by this. I've used pandas for ever a decade, and in most cases it's a massive time saver. I can do a single query to bring data into local memory in a Jupyter notebook, and from there re-use that memory across hundreds or more executions of pandas functions to further refine an analysis or whatever task I'm up to.

Your "copy-object-copy" is not relevant in data analysis use cases, and exists in pretty much any system that pulls data from an external service be it SQL or not.

Well it's true that bringing row(s) of data from database to python and then serializing it for any reason as has extra overhead.

But in Django's case, the same arguments can be made as for Pandas. Django is a big complex framework and an application using it might consume more memory due to countless number of other reasons. There are also best practices to use Django ORM.

But to say if a given Django instance consumes more memory it is only because of "Copy-Object-Copy effect"... I don't think so.

Interesting. I'm guessing the extra layer may be needed for some manipulations of data at the application layer, but perhaps that is almost always avoidable?

Have you ever tried Hasura? I'm thinking of giving it a go in a future project.

I've been doing a lot of data analysis in Pandas recently. I started off thinking that for efficiency's sake, I should do as much initial processing in the DB as possible, and use Pandas just for the higher level functions that were difficult to do in SQL.

But after some trial and error, I find it much faster to pull relatively large, unprocessed datasets and do everything in Pandas on the local client. Faster both in total analysis time, and faster in DB cycles.

It seems like a couple of simple "select * from cars" and "select * from drivers where age < 30", and doing all the joining, filtering, and summarizing on my machine, is often less burdensome on the db than doing it up-front in SQL.

Of course, this can change depending on the specific dataset, how big it is, how you're indexed, and all that jazz. Just wanted to mention how my initial intuition was misguided.

Does `tablefunc.crosstab()` do what you want?

https://www.postgresql.org/docs/13/tablefunc.html

I've always been disappointed by the SQL pivot. It's hardly useful for me if I have to know up-front all of the columns it's going to pivot out into. The solution would be to use another SQL query to generate a dynamic SQL query, but at that point I would rather just use Pandas

After 15 years programming I have come to love SQL - 10 yrs ago I used to do everything in the book to avoid it out of hate, even though I knew how to use it, but over time I have moved more and more into the database / SQL and my code is just better and better as a result.

Can you honestly say you'd prefer to be debug thousands of lines of SQL versus the usual <100 lines of Python/pandas that does the same thing? It's no contest. A histogram in pandas: df.col.hist(). Unique values: df.col.value_counts(). Off the top of your head, what is the cleanest way of doing this in SQL and how does it compare? How anyone can say that SQL is objectively better (in readability, density, any metric) other than the fact that they learned it first and now are frustrated that they have to learn another new tool baffles me.

I learned Pandas first. I have no issue with indexing, different ways of referencing cells, modifying individual rows and columns, numerous ways of slicing and dicing. It gets a little sprawling but there's a method to the madness. I can come back to it months later and easily debug. With SQL, it's just madness and 10x more verbose.

I much prefer writing actual functions in a real programming language to the verbose non-reusable non-composable relic that is SQL syntax.

Give me a better query language and I will gladly drop Pandas and Data.Table.

You might be thinking of specific functionality that you find is being implemented in an overly long/verbose fashion.. But generally speaking, how are

> long lines of().chained().['expressions'].like_this(0)

a _bad thing_?

IMHO these pandas chains are easy to read and communicate quite clearly what's being done. If anything, I've found that in my day-to-day while reading pandas I parse the meaning of those chains at least as efficiently as from comments of any level of specificity, or from what other languages (that I have had experience with) would've looked like.

This really grinds my gears too. There's something about the pandas API that makes it impossible for me to do basic ops without tedious manual browsing to get inplace or index arguments right... assignments and conditionals are needlessly verbose too.

Pyspark on the other hand just sticks in my brain, somehow. Chained pyspark method calls looks much neater.

Completely disagree. I have a choice of using snowflake and spark/pandas to do my EDA and I’ll choose sql every time. The code is significantly more readable once you get used to it and you can most definitely do things one step at a time using udfs and temp tables / cte s. I’ve come back to EDA I did a year back and it’s always easier to read a long sql script than a notebook with pandas code.

> SQL syntax sucks for doing non-trivial data analysis. I've tried it. Verbose, no composability or code reuse, not really portable across different databases, no easy interoperability with other tools, limited editor/IDE support, etc.

You're entitled to your opinion and tooling choices of course, but the problem is you don't know SQL.

> I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL.

This is not huge. This is quite small. Pandas can't handle data that runs into billions of rows or sub-second response on arbitrary queries. Both are common requirements in many analytic applications.

I like Pandas. It's flexible and powerful if you have experience with it. But there's no question that SQL databases (especially data warehouses) handle large datasets and low latency response far better than anything in the Python ecosystem.

For apache arrow backed dataframes check out Polars(https://github.com/ritchie46/polars)

We have a lot more clueless developers on average, which makes it seem like no one uses SQL anymore beyond basics. But we're still here, believe me. No one today is a dedicated X programmer, but doesn't mean we're all clueless about X.

I started learning SQL twenty years ago and it's one of the only skills from the start of my career that has been consistently useful ever since.

I held this view before graduating and moving back to my 3rd world home country. For some reason, people here (the established ones with 5+ years of experience) see SQL as a general purpose programming language with which they do as much as possible.

> The days of dedicated SQL programers are mostly gone.

I've never met a "dedicated SQL programmer" - all the C++ programmers I've worked with in the investment banking world were also expected to know, and did know, SQL pretty well - asking questions about it were normally part of the interview process.