python - SQLite 和 PROC SQL 在评估空格时的区别

Question

我正在比较两个数据集以查找某些列上的重复条目。

我首先在 SAS 中使用以下查询使用 PROC SQL 命令完成了此操作(我认为是真实结果):

proc sql;
CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND a.yob1 = b.yob2
    AND a.cob1 = b.cob2;
quit;

我将此结果输出到 csv，给出 output_sas.csv

我也在 Python 中使用 SQLite3 使用相同的查询完成了此操作:

conn = sqlite3.connect(file_path + db_name)
cur = conn.cursor()
cur.execute("""
CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND a.yob1 = b.yob2
    AND a.cob1 = b.cob2
""")

我将其输出到 csv，给出 output_python.csv。

问题:

输出应该是相同的，但实际上不是:

output_sas.csv 比 output_python.csv 多包含 123 条记录。

在 SAS 输出文件中，yob1 和 yob2 列中有 123 条包含空格 "" 的记录，例如, sas_data.csv 中的 123 条记录如下所示:

yob1 yob2 cob1 cob2 surname1 surname2
""     ""     1    1    xx       xx
""     ""     2    2    yy       yy
.
.
.
# Continues for 123 records

我发现这种差异是由于 yob1 和 yob2 列，在上面的 123 条记录中包含空格。 output_python.csv 文件中缺少这 123 个记录对。

[注:在这项工作中，长度为零的字符串对应一个缺失值]

简而言之:

SAS 中的 PROC SQL 例程正在评估空格是否相等，即 ""== ""-> TRUE。

Python SQLite 代码似乎在做相反的事情，即 ""== ""->
错误

即使 ""== ""-> True 在 Python 中也会发生这种情况。

问题:

为什么会这样？我需要更改什么才能使 SQLite 输出与 PROC SQL 输出相匹配？

注意:两个例程都使用相同的输入数据集。它们完全相等，我什至手动修改 Python 代码以确保 yob1 和 yob2 列包含 "" 缺失值。

更新 1:

目前我的 SAS PROC SQL 代码使用 data1.sas7bdat，命名为 local 和 data2.sas7bdat，命名为 邻居。

为了在 Python 和 SAS 中使用相同的数据集，我将它们导出到 csv 并读入 Python。

如果我这样做:

import pandas as pd
# read in
dflocal     = pd.read_csv(csv_path_local, index_col=False)
dfneighbor  = pd.read_csv(csv_path_neighbor, index_col=False)

Pandas 将缺失值转换为 nan。我们可以使用 isnull() 来查找每一列中 nan 值的数量:

# find null / nan values in yob1 and yob2 in each dataset
len(dflocal.loc[dflocal.yob1.isnull()])
78
len(dfneighbor.loc[dfneighbor.yob2.isnull()])
184

为了解决空值问题，我随后通过运行将 nan 显式转换为长度为零的字符串 "":

dflocal['yob1'].fillna(value="", axis=0, inplace=True)
dfneighbor['yob2'].fillna(value="", axis=0, inplace=True)

我们可以通过测试已知的 nan 来测试值是否更新:

dflocal.iloc[393].yob1
`""`
type(dflocal.iloc[393].yob1)
str

所以它们是长度为0的字符串。

然后通过以下方式将这些读入 SQL:

dflocal.to_sql('local', con=conn, flavor='sqlite', if_exists='replace', index=False)
dfneighbor.to_sql('neighbor', con=conn, flavor='sqlite', if_exists='replace', index=False)

然后执行相同的 SQLite3 代码:

conn = sqlite3.connect(file_path + db_name)
cur = conn.cursor()
cur.execute("""
CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND a.yob1 = b.yob2
    AND a.cob1 = b.cob2
""")

即使我进行了此显式更改，我仍然 得到相同的缺失 123 个值，尽管空值已更改为长度为零的字符串 "".

可能的解决方案:

但是，如果我改为使用 na_filter=False 参数导入数据集，这会为我完成从 null 到 "" 的转换.

dflocal     = pd.read_csv(csv_path_local, index_col=False, na_filter=False)
dfneighbor  = pd.read_csv(csv_path_neighbor, index_col=False, na_filter=False")

# find null / nan values in yob1 and yob2 in each dataset

len(dflocal.loc[dflocal.yob1.isnull()])
0
len(dfneighbor.loc[dfneighbor.yob2.isnull()])
0

当我将这些数据集导入我的数据库并通过相同的 SQL 代码运行时:

conn = sqlite3.connect(file_path + db_name)
cur = conn.cursor()
cur.execute("""
CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND a.yob1 = b.yob2
    AND a.cob1 = b.cob2
""")

万岁，我得到了与 SAS 代码相同的输出!

但为什么第一个解决方案不起作用？我在这两种情况下都做同样的事情(第一种情况是使用 fill_na 手动完成，第二种情况是使用 na_filter=False)。

Answer 1

在 SAS 中，实际上并没有字符空值的概念。它更像是一个空字符串。然而，在大多数 SQL 实现中(包括 SQlite，我假设)，空值和空字符串是不同的。

SAS 中的空白值确实被评估为 ""= ""，即 true

然而，在您的普通 DBMS 中，您所谓的“空值”通常是 null 值，而不是空字符串 ("")。 并且 null=null 不是真的。您不能将空值与任何内容进行比较，包括空值。

您可以做的是将您的 SQlite 更改为

CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND coalesce(a.yob1,'') = coalesce(b.yob2,'')
    AND a.cob1 = b.cob2

当 yob 为 null 时，coalesce 函数会将 yob 替换为空字符串。

但是请注意，如果 yob1 为 null 而 yob2 实际上是一个空字符串，添加这些合并函数将改变 null= '' 条件(不为真)转换为 ''='' 为真。如果那不是你想要的，你也可以这样写:

CREATE TABLE t1 AS
SELECT a.*, b.*
FROM
    local AS a INNER JOIN neighbor AS b
    ON a.surname1 = b.surname2
    AND (a.yob1 = b.yob2
        OR (a.yob1 is null AND a.yob2 is null)
        )
    AND a.cob1 = b.cob2