CTEs can indeed align mismatched and denormalised storage to some E/R which follows some business logic. That much you can say. But CTEs allow for recursion and save time when you need complexity but you want to follow a precise step-by-step reasoning.
This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews.
You can have arbitrary dimensions sliced in CTEs which does not immediately imply a goal of business schema alignment.
Besides the top SQL devs I’ve met don’t lose time to align schema at all, but write the DB table names and columns as they are, because well… because they were usually the people who created the mess this way.
Many reports such as month-to-month increase of sales or other statistical stuff is much more readable when implemented with CTes. Besides - older versions of DB software didn’t always have window functions or proper ranking, so problems such as top-n were not (and in some occasions still are not) trivial to write unless view/cte is used. We talking pages of single query here, not textbook examples.
So is really about dice and slice in a convenient way, but less about business schema or the ideal E/R
Maybe I misunderstood, and if I did it makes for an interesting discussion, but your explanation is mainly about what CTEs can be used for not what they can not be used for.
In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.
CTEs can indeed align mismatched and denormalised storage to some E/R which follows some business logic. That much you can say. But CTEs allow for recursion and save time when you need complexity but you want to follow a precise step-by-step reasoning.
This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews.
You can have arbitrary dimensions sliced in CTEs which does not immediately imply a goal of business schema alignment.
Besides the top SQL devs I’ve met don’t lose time to align schema at all, but write the DB table names and columns as they are, because well… because they were usually the people who created the mess this way.
Many reports such as month-to-month increase of sales or other statistical stuff is much more readable when implemented with CTes. Besides - older versions of DB software didn’t always have window functions or proper ranking, so problems such as top-n were not (and in some occasions still are not) trivial to write unless view/cte is used. We talking pages of single query here, not textbook examples.
So is really about dice and slice in a convenient way, but less about business schema or the ideal E/R
Maybe I misunderstood, and if I did it makes for an interesting discussion, but your explanation is mainly about what CTEs can be used for not what they can not be used for.
In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.
That makes sense. I misunderstood your OP.
I am happy you understood! I still feel this discussion is above my head, so if you are still there i would love an explanation!