sql 多列join_SQL的弱点（2）：不支持从所有列中去除某列-CSDN博客

博客指出SQL中选择所有列虽方便但性能有问题，且从所有列中去除或更新某一列在多数数据库较难支持。介绍了Google BigTable支持的“SELECT * EXCEPT”和 “SELECT * REPLACE”语法，以及Apache Spark的处理方式，还提出非Google BigQuery数据库可借助程序实现相关操作。

背景以及场景

我们知道SQL中有一个非常便利的操作：

SELECT *
FROM TABLE_NAME

这样可以选择出表中所有的列，而不用一个个列出来。虽然这个从性能角度是有很大问题的，但是架不住它方便，所以被广泛使用。

以及延展的快速加个新列都很方便：

SELECT *, 1 AS new_column
FROM TABLE_NAME

但是，无论在SQL标准中，还是在绝大多数数据库中，有一类日常很容易出现的场景在SQL中却比较难支持：

从所有列中去除某一列
从所有列中只更新某一列，这个和“去除一列”是一类问题

这时，我们一般会是从数据库中查出该表所有的列，手工去掉某一列, 然后在查询时明确指定所需的列，比如：对于前文中的“customers”表：

idcustomer_idproduct_idunitunit_pricediscount11110100.22125500.33218150.243322150.1

我们要去掉 discount 列，我们需要写SQL为：

SELECT id,
customer_id,
product_id,
unit,
unit_price
FROM customers

也许有人说：明确列出所有列更清晰也性能更好，不过在日常使用中确实不方便，因为：

数仓中，经常有某些表有非常多列（300+列）的情况，尤其是有不少数据库因为性能和避免JOIN都推荐用“大宽表”的形式，真的把这些表的列名列出来真的好繁琐。也许你熟练 EMACS 等编辑器神器，可以用其中的 KeyboardMacros 等秘技来简化文本操作，但是还是非常不方便和容易出错。
尤其是SQL比较复杂后（比如前文中的意大利面式SQL），往往这些列的列表会出现在多个地方，这时候要是万一底层表多了一列，那这列非常难扩散到SQL最终结果中。

少数几个数据库的解决方案

Google BigTable 支持 "SELECT * EXCEPT" 和 “SELECT * REPLACE”

参考 Google BigQuery 的语法：

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

可以用

SELECT * EXCEPT discount
FROM customers

来快速去掉列。

而用

SELECT * REPLACE (0.8 * discount) AS discount
FROM customers

来对 discount 列进行更新

从这点来说，Google BigQuery 的查询确实挺人性化，但是遗憾的是 Google BigQuery 对国人来说基本上等同于“不存在”。

Apache Spark 的方式

Spark SQL不光是能支持直接使用SQL的形式，还能直接Python/Scala/Java等调用其 Dataset API，那就可以用：

df.drop("discount")

来删除列

而用

df.withColumn("discount",
functions.expr("0.8 * discount"))

来重命名

可是其它大多数数据库不支持（比如：Postgresql）

对于非 Google BigQuery 数据库，程序来帮忙

如果我们采取前文中的借助程序来做

YAML文件（包含了各个小的逻辑SQL） -> 面向机器的SQL

的转化的话，那么，我们可以额外增加一些非SQL操作节点，比如：

Steps:
- name: step_filter_customer1
comment: 过滤掉非法客户
sql: |-
SELECT *
FROM customers
WHERE customers.is_delete=False
- name: step_update_distinct
type: SELECT_ALL_REPLACE
from: step_filter_customer1
columns:
- column: discount
expr: 0.8 * discount
- name: step_drop_distinct
type: SELECT_ALL_EXCEPT
from: step_update_distinct
columns:
- "distinct"

那我们的转换程序就可以额外多做一点处理，通过程序读取出之前表的所有列，这样当执行 SELECT_ALL_REPLACE 和 SELECT_ALL_EXCEPT 时，就可以提人工去实现 Google BigQuery 的 “SELECT * REPLACE” 和 “SELECT * EXCEPT” 了。

讨论SQL的弱点，不是说SQL不好，而是为了SQL更好！