上文最后提到，用pyxlsbwriter 写入比用xlsxwriter写入的xlsx数据，执行查询时多了个第49号查询的错误，现在来分析原因。
load tpcds;
call dsdgen(sf=0.1);
原始生成的catalog_sales表数据
D from catalog_sales limit 20;
┌─────────────────┬─────────────────┬─────────────────┬─────────────────────┬───┬─────────────────────┬──────────────────────┬──────────────────────┬───────────────┐
│ cs_sold_date_sk │ cs_sold_time_sk │ cs_ship_date_sk │ cs_bill_customer_sk │ … │ cs_net_paid_inc_tax │ cs_net_paid_inc_ship │ cs_net_paid_inc_sh… │ cs_net_profit │
│ int32 │ int32 │ int32 │ int32 │ │ decimal(7,2) │ decimal(7,2) │ decimal(7,2) │ decimal(7,2) │
├─────────────────┼─────────────────┼─────────────────┼─────────────────────┼───┼─────────────────────┼──────────────────────┼──────────────────────┼───────────────┤
│ 2450815 │ 38212 │ 2450886 │ 2153 │ … │ 2121.55 │ 3062.05 │ 3163.07 │ 718.63 │
│ 2450815 │ 38212 │ 2450846 │ 2153 │ … │ 1856.89 │ 3142.08 │ 3213.49 │ 34.48 │
│ 2450815 │ 38212 │ 2450868 │ 2153 │ … │ 589.49 │ 724.66 │ 758.02 │ -771.21 │
│ 2450815 │ 38212 │ 2450851 │ 2153 │ … │ 9811.88 │ 13741.50 │ 14118.88 │ 5934.50 │
│ 2450815 │ 29485 │ 2450863 │ 4601 │ … │ 1988.87 │ 4997.82 │ 5162.03 │ -1580.38 │
│ 2450815 │ 29485 │ 2450832 │ 4601 │ … │ 916.20 │ 1304.99 │ 1340.22 │ -1729.75 │
│ 2450815 │ 29485 │ 2450842 │ 4601 │ … │ 733.09 │ 1655.93 │ 1663.18 │ -124.27 │
│ 2450815 │ 29485 │ 2450882 │ 4601 │ … │ 1891.24 │ 2198.11 │ 2253.19 │ 502.93 │
│ 2450815 │ 29485 │ 2450870 │ 4601 │ … │ 2133.67 │ 10374.75 │ 10550.92 │ -6268.35 │
│ 2450815 │ 29485 │ 2450879 │ 4601 │ … │ 416.61 │ 499.50 │ 515.52 │ 26.46 │
│ 2450815 │ 29485 │ 2450873 │ 4601 │ … │ 2463.58 │ 3019.10 │ 3067.40 │ -1244.50 │
│ 2450815 │ 29485 │ 2450850 │ 4601 │ … │ 147.84 │ 186.33 │ 186.33 │ 57.24 │
│ NULL │ NULL │ 2450826 │ 4601 │ … │ NULL │ 4780.11 │ 5164.73 │ -3207.45 │
│ 2450815 │ 29485 │ 2450854 │ 4601 │ … │ 10077.60 │ 11501.40 │ 11501.40 │ 6108.60 │
从xlsx文件读入数据，第一列第14行引发解析错误，忽略错误后保存为NULL，与原数据一致。
D create table catalog_sales as from read_xlsx('source.xlsx',sheet='catalog_sales');
Invalid Input Error:
read_xlsx: Failed to parse cell 'A14': Could not convert string '<NA>' to DOUBLE
D create table catalog_sales as from read_xlsx('source.xlsx',sheet='catalog_sales',ignore_errors=1);
D from catalog_sales limit 20;
┌─────────────────┬─────────────────┬─────────────────┬─────────────────────┬───┬─────────────────────┬──────────────────────┬──────────────────────┬───────────────┐
│ cs_sold_date_sk │ cs_sold_time_sk │ cs_ship_date_sk │ cs_bill_customer_sk │ … │ cs_net_paid_inc_tax │ cs_net_paid_inc_ship │ cs_net_paid_inc_sh… │ cs_net_profit │
│ double │ double │ double │ double │ │ double │ double │ double │ double │
├─────────────────┼─────────────────┼─────────────────┼─────────────────────┼───┼─────────────────────┼──────────────────────┼──────────────────────┼───────────────┤
│ 2450815.0 │ 38212.0 │ 2450886.0 │ 2153.0 │ … │ 2121.55 │ 3062.05 │ 3163.07 │ 718.63 │
│ 2450815.0 │ 38212.0 │ 2450846.0 │ 2153.0 │ … │ 1856.89 │ 3142.08 │ 3213.49 │ 34.48 │
│ 2450815.0 │ 38212.0 │ 2450868.0 │ 2153.0 │ … │ 589.49 │ 724.66 │ 758.02 │ -771.21 │
│ 2450815.0 │ 38212.0 │ 2450851.0 │ 2153.0 │ … │ 9811.88 │ 13741.5 │ 14118.88 │ 5934.5 │
│ 2450815.0 │ 29485.0 │ 2450863.0 │ 4601.0 │ … │ 1988.87 │ 4997.82 │ 5162.03 │ -1580.38 │
│ 2450815.0 │ 29485.0 │ 2450832.0 │ 4601.0 │ … │ 916.2 │ 1304.99 │ 1340.22 │ -1729.75 │
│ 2450815.0 │ 29485.0 │ 2450842.0 │ 4601.0 │ … │ 733.09 │ 1655.93 │ 1663.18 │ -124.27 │
│ 2450815.0 │ 29485.0 │ 2450882.0 │ 4601.0 │ … │ 1891.24 │ 2198.11 │ 2253.19 │ 502.93 │
│ 2450815.0 │ 29485.0 │ 2450870.0 │ 4601.0 │ … │ 2133.67 │ 10374.75 │ 10550.92 │ -6268.35 │
│ 2450815.0 │ 29485.0 │ 2450879.0 │ 4601.0 │ … │ 416.61 │ 499.5 │ 515.52 │ 26.46 │
│ 2450815.0 │ 29485.0 │ 2450873.0 │ 4601.0 │ … │ 2463.58 │ 3019.1 │ 3067.4 │ -1244.5 │
│ 2450815.0 │ 29485.0 │ 2450850.0 │ 4601.0 │ … │ 147.84 │ 186.33 │ 186.33 │ 57.24 │
│ NULL │ NULL │ 2450826.0 │ 4601.0 │ … │ nan │ 4780.11 │ 5164.73 │ -3207.45 │
│ 2450815.0 │ 29485.0 │ 2450854.0 │ 4601.0 │ … │ 10077.6 │ 11501.4 │ 11501.4 │ 6108.6 │
执行第49号查询报错
Conversion Error: Could not cast value nan to DECIMAL(15,4) when casting from source column sum(COALESCE(cr_return_amount, CAST(0 AS DOUBLE)))
LINE 54: (cast(sum(coalesce(cr.cr_return_amount,0)) AS decimal(15...
^
再查看SQL原文，
(SELECT cs.cs_item_sk AS item,
(cast(sum(coalesce(cr.cr_return_quantity,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS decimal(15,4))) AS return_ratio,
(cast(sum(coalesce(cr.cr_return_amount,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS decimal(15,4))) AS currency_ratio
FROM catalog_sales cs
LEFT OUTER JOIN catalog_returns cr ON (cs.cs_order_number = cr.cr_order_number
AND cs.cs_item_sk = cr.cr_item_sk) ,date_dim
cr别名引用的是catalog_returns表，再读取catalog_returns表，
原始数据
D from catalog_returns limit 20;
┌─────────────────────┬─────────────────────┬────────────┬──────────────────────┬───┬──────────────────┬────────────────────┬─────────────────┬──────────────┐
│ cr_returned_date_sk │ cr_returned_time_sk │ cr_item_sk │ cr_refunded_custom… │ … │ cr_refunded_cash │ cr_reversed_charge │ cr_store_credit │ cr_net_loss │
│ int32 │ int32 │ int32 │ int32 │ │ decimal(7,2) │ decimal(7,2) │ decimal(7,2) │ decimal(7,2) │
├─────────────────────┼─────────────────────┼────────────┼──────────────────────┼───┼──────────────────┼────────────────────┼─────────────────┼──────────────┤
│ 2450861 │ 58939 │ 526 │ 4601 │ … │ 113.43 │ 65.79 │ 90.86 │ 368.99 │
│ 2450985 │ 79214 │ 608 │ 4601 │ … │ 356.52 │ 14.10 │ 29.97 │ 251.47 │
│ 2450901 │ 22932 │ 826 │ 7572 │ … │ 602.27 │ 582.81 │ 1131.36 │ 336.64 │
│ 2450939 │ 36548 │ 964 │ 7572 │ … │ 38.11 │ 125.27 │ 90.72 │ 117.93 │
│ 2451043 │ 65563 │ 1315 │ 1351 │ … │ 3.64 │ 9.07 │ 8.73 │ 153.90 │
│ 2450980 │ 41818 │ 250 │ 7674 │ … │ 209.00 │ 66.18 │ 595.69 │ 119.86 │
│ 2450995 │ 9143 │ 361 │ 9276 │ … │ 1005.01 │ 241.20 │ 231.75 │ 535.34 │
│ 2451025 │ 18121 │ 1351 │ 9276 │ … │ 56.99 │ 9.26 │ 3.26 │ 116.82 │
│ 2450908 │ 26998 │ 1048 │ 1731 │ … │ 89.15 │ 71.77 │ 953.57 │ 670.60 │
│ 2450998 │ 50087 │ 850 │ 1731 │ … │ 77.61 │ 69.07 │ 558.92 │ 192.06 │
│ 2451001 │ 40271 │ 538 │ 6748 │ … │ 186.17 │ 99.12 │ 168.79 │ 27.56 │
│ 2451000 │ 34882 │ 883 │ 6950 │ … │ 637.46 │ 182.80 │ 7.62 │ 96.79 │
│ 2451012 │ 62844 │ 1016 │ 4957 │ … │ 379.67 │ 6925.29 │ 288.56 │ 4009.95 │
│ 2450915 │ 45403 │ 139 │ 2003 │ … │ NULL │ 88.69 │ NULL │ NULL │
D create table catalog_returns as from read_xlsx('source.xlsx',sheet='catalog_returns',ignore_errors=1);
D from catalog_returns limit 20;
┌─────────────────────┬─────────────────────┬────────────┬──────────────────────┬───┬──────────────────┬────────────────────┬─────────────────┬─────────────┐
│ cr_returned_date_sk │ cr_returned_time_sk │ cr_item_sk │ cr_refunded_custom… │ … │ cr_refunded_cash │ cr_reversed_charge │ cr_store_credit │ cr_net_loss │
│ double │ double │ double │ double │ │ double │ double │ double │ double │
├─────────────────────┼─────────────────────┼────────────┼──────────────────────┼───┼──────────────────┼────────────────────┼─────────────────┼─────────────┤
│ 2450861.0 │ 58939.0 │ 526.0 │ 4601.0 │ … │ 113.43 │ 65.79 │ 90.86 │ 368.99 │
│ 2450985.0 │ 79214.0 │ 608.0 │ 4601.0 │ … │ 356.52 │ 14.1 │ 29.97 │ 251.47 │
│ 2450901.0 │ 22932.0 │ 826.0 │ 7572.0 │ … │ 602.27 │ 582.81 │ 1131.36 │ 336.64 │
│ 2450939.0 │ 36548.0 │ 964.0 │ 7572.0 │ … │ 38.11 │ 125.27 │ 90.72 │ 117.93 │
│ 2451043.0 │ 65563.0 │ 1315.0 │ 1351.0 │ … │ 3.64 │ 9.07 │ 8.73 │ 153.9 │
│ 2450980.0 │ 41818.0 │ 250.0 │ 7674.0 │ … │ 209.0 │ 66.18 │ 595.69 │ 119.86 │
│ 2450995.0 │ 9143.0 │ 361.0 │ 9276.0 │ … │ 1005.01 │ 241.2 │ 231.75 │ 535.34 │
│ 2451025.0 │ 18121.0 │ 1351.0 │ 9276.0 │ … │ 56.99 │ 9.26 │ 3.26 │ 116.82 │
│ 2450908.0 │ 26998.0 │ 1048.0 │ 1731.0 │ … │ 89.15 │ 71.77 │ 953.57 │ 670.6 │
│ 2450998.0 │ 50087.0 │ 850.0 │ 1731.0 │ … │ 77.61 │ 69.07 │ 558.92 │ 192.06 │
│ 2451001.0 │ 40271.0 │ 538.0 │ 6748.0 │ … │ 186.17 │ 99.12 │ 168.79 │ 27.56 │
│ 2451000.0 │ 34882.0 │ 883.0 │ 6950.0 │ … │ 637.46 │ 182.8 │ 7.62 │ 96.79 │
│ 2451012.0 │ 62844.0 │ 1016.0 │ 4957.0 │ … │ 379.67 │ 6925.29 │ 288.56 │ 4009.95 │
│ 2450915.0 │ 45403.0 │ 139.0 │ 2003.0 │ … │ nan │ 88.69 │ nan │ nan │
可以观察到，cr_refunded_cash等3列选中的最后1行，有3个nan值，而原始数据是NULL, 不一致，这就是导致错误的原因，实际上，前面catalog_sales表的cs_net_paid_inc_tax也有nan值，也与原始数据不一致，虽然没有引发报错，但也有隐患。
虽然duckdb可以处理nan，比如：
SELECT sqrt(2) > '-inf', 'nan' > sqrt(2);
┌────────────────────┬───────────────────┐
│ (sqrt(2) > '-inf') │ ('nan' > sqrt(2)) │
│ boolean │ boolean │
├────────────────────┼───────────────────┤
│ true │ true │
└────────────────────┴───────────────────┘
但tpcds的SQL并没有针对nan编写，因为原始数据就不存在nan。
再查看pyxlsbwriter的xlsx_writer.py源码, 跟NaN有关的有这几行，其实对平常的SQL计算没有意义，直接都处理成NULL, 就可以了。
# Handle special float values
if isinstance(cell, float):
if cell != cell: # NaN
self._write_string_cell(buffer, "NaN", row_idx == 0)
continue
elif cell == float('inf'):
self._write_string_cell(buffer, "∞", row_idx == 0)
continue
elif cell == float('-inf'):
self._write_string_cell(buffer, "-∞", row_idx == 0)
continue
恰好duckdb的excel插件处理逻辑是:
ignore_errors BOOLEAN false Whether to ignore errors and silently replace cells that cant be cast to the corresponding inferred column type with NULL’s.
它觉得NaN是能转换成对应数据类型的，就直接读入，而不是转换成NULL，反而造成了问题。
pyxlsbwriter还存在把所有整数都识别成浮点数问题，看来它为追求速度，牺牲了一部分准确度，需要改进。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
