業(yè)務(wù)數(shù)據(jù)經(jīng)常存在層次關(guān)系，比如訂單包含日期、客戶和訂單明細(xì)，訂單明細(xì)又包含價格、數(shù)量、產(chǎn)品，產(chǎn)品則包含名稱和分類。

json 的可嵌套結(jié)構(gòu)很適合描述這種關(guān)系，比如訂單數(shù)據(jù)：

css
體驗AI代碼助手
代碼解讀
復(fù)制代碼
[   {   "order_id": 10248,"order_date": "2022-07-04","freight_rate": 2,"weight": 5,        "order_details": [            {   "price": 14.0,"quantity": 12.0,			    "product": {"product_id": 17,"name": "beef","category":"Food"},            },            {   "price": 9.0,"quantity": 10.0			    "product": {"product_id": 42,"name": "rice","category":"Food"}            }        ],
        "customer": {
            "customer_id": 87,"name": "abc inc",
            "city": "New York","phone_number": "26471510"
        }        
    },
    {   "order_id": 10249,"order_date": "2012-07-05","freight_rate": 10,"weight": 3,
        "order_details": [
            {   "price": 18.0,"quantity": 9.0
			    "product": {"product_id": 14,"name": "Computer","category": "Electronic"},
            }
        ],
        "customer": {
            "customer_id": 991,"name": "bcd.com",
            "city": "Los Angeles","phone_number": "(0251) 1031259"
        }
     }
...]

訂單既有普通屬性“訂單號、日期、運費單價、重量”，也包括嵌套屬性“訂單明細(xì)、客戶”，訂單明細(xì)又包含嵌套屬性“產(chǎn)品”。

對于這種可嵌套結(jié)構(gòu)，常見的數(shù)據(jù)組織方式是多層嵌套表，即字段可以是表或記錄。計算時，無論哪一層的表（記錄），都是對象，都能一致運算。

但 SQL 天生只有平面表，無法實現(xiàn)這種方式。某些新型分析數(shù)據(jù)庫在一定程度上支持嵌套結(jié)構(gòu)，可以處理 json 數(shù)據(jù)。

比如要在訂單中查出 order_id, order_date, 以及嵌套屬性 customer 的 name 和 city，用 DuckDB 這樣寫：

csharp
體驗AI代碼助手
代碼解讀
復(fù)制代碼
select order_id, order_date, customer.name, customer.city
from read_json_auto('orders.json')

這是把單行嵌套字段 customer 看成記錄，用記錄的方式取出其字段。但這種方法只能用于單行嵌套字段，多行的 order_details 就不支持了，這樣寫只能取出空值。

對多行嵌套字段，DuckDB 可以展開后計算。比如要在這個查詢基礎(chǔ)上，對 order_detail 中 quantity*price 匯總求和，得到訂單金額 amount：

scss
體驗AI代碼助手
代碼解讀
復(fù)制代碼
select order_id,any_value(order_date),
    any_value(customer.name),any_value(customer.city),
    sum(od.quantity * od.price) as amount
from read_json_auto('orders.json') as o,
    lateral unnest(o.order_details) as t(od)
group by order_id;

先展開 order_details，與 orders 隱式 join 成大平面表，然后按照訂單號分組匯總求和，代碼很繞。這樣做，還不如轉(zhuǎn)換成物理的平面表，計算時還能省去“展開”這個步驟。

DuckDB 也可以保持嵌套結(jié)構(gòu)，使用 lambda 語法計算：

csharp
體驗AI代碼助手
代碼解讀
復(fù)制代碼
select order_id, order_date,customer.name, customer.city,
    list_sum( list_transform(o.order_details,x -> x.price * x.quantity) ) as amount
from read_json_auto('orders.json') as o

要使用兩個特殊函數(shù)，還要寫顯式的 lambda 語法，略顯啰嗦。

對比一下，普通數(shù)據(jù)表做類似計算并沒有這么麻煩。比如求訂單運費單價和重量相乘再匯總的代碼是這樣：

scss
體驗AI代碼助手
代碼解讀
復(fù)制代碼
select sum(freight_rate*weight) from read_json_auto('orders.json')

語法完全不一致。

實際上，DuckDB 把多行嵌套字段當(dāng)成數(shù)組，而不是數(shù)據(jù)表。這就造成運算上的不一致，給代碼書寫和閱讀都會帶來麻煩。

esProc SPL 徹底實現(xiàn)了前面說的多層嵌套表機(jī)制，可以寫出最簡潔的代碼：

less
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders=json(file("orders.json").read())
orders.new(order_id,order_date,customer.name,customer.city,order_details.sum(price*quantity):amount)

對比一下計算總運費的代碼：

python
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders.sum(freight_rate*weight)

語法完全一致！

字段 order_details 與 orders 一樣都是表對象，計算方法都一致。有這樣簡潔、一致的語法，SPL 處理可嵌套結(jié)構(gòu)的數(shù)據(jù)，可以說是最專業(yè)的了。

Python 也支持多層結(jié)構(gòu)，對于單行的嵌套字段，可以這樣計算：

ini
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders = pd.read_json('orders.json')
result_orders = orders[['order_id', 'order_date']].assign(
    customer_name=orders['customer'].str['name'],
    customer_city=orders['customer'].str['city']
)

取 customer 的字段多了個函數(shù) str，不如 DuckDB 和 SPL 簡潔。而且 str 函數(shù)僅支持可以轉(zhuǎn)為字符串的簡單類型，不支持多行的嵌套字段。

Python 也可以把多行的嵌套字段展開計算：

ini
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders = pd.read_json('orders.json')
exploded_orders = orders.explode('order_details')
exploded_orders = pd.concat([exploded_orders,exploded_orders['order_details'].apply(pd.Series)], axis=1)

exploded_orders['amount'] = exploded_orders['price'] * exploded_orders['quantity']
result = exploded_orders.groupby('order_id').agg({'order_date':'first','amount': 'sum'}).reset_index()
final_result = result[['order_id', 'order_date', 'amount']]

先縱向展開 order_details，再橫向展開每行的字段，變成平面表后，再計算分組匯總，這也很繞。多行嵌套結(jié)構(gòu)還要展開兩次，比 DuckDB 更啰嗦。

Python 也能保持嵌套結(jié)構(gòu)，用 lambda 計算：

less
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders = pd.read_json('orders.json')
orders['amount'] = orders['order_details'].apply(lambda details:sum(item['price'] * item['quantity'] for item in details))
result=orders[['order_id','order_date','amount']]

Python 是顯式 lambda 語法，要寫 lambda 關(guān)鍵字，還要定義參數(shù)，有點啰嗦。而且 lambda 函數(shù)中還要寫顯式的 for 循環(huán)，相當(dāng)于兩重循環(huán)，會增加理解的難度。這里的 for 雖然是簡化寫法，也還是要定義循環(huán)變量 item，還是啰嗦。

我們也對比一下計算運費的代碼：

css
體驗AI代碼助手
代碼解讀
復(fù)制代碼
total_freight=sum(orders['freight_rate']*orders['weight'])

語法也是完全不一致。

order_details 并不是 orders 那樣的 Dataframe 對象，而是類似數(shù)組的列表類型。

在稍復(fù)雜的情況下，DuckDB 和 Python 的語法不一致等問題帶來的麻煩會更明顯，比如找出總金額大于 200，而且還包含 Electronics 類產(chǎn)品的訂單，取得 order_id、order_date。

DuckDB 如果采用展開嵌套字段的方式，SQL 就比較難寫了。展開 order_details、product 之后要再分組聚合計算訂單金額，然后再基于這個結(jié)果篩選符合條件的訂單，要寫多層子查詢或者 CTE 來保持?jǐn)?shù)據(jù)結(jié)構(gòu)的完整性。寫出來的 SQL 會很長，調(diào)試起來就不太友好了，這里不再給出，只給出 lambda 的寫法：

csharp
體驗AI代碼助手
代碼解讀
復(fù)制代碼
select order_id, order_date
from read_json_auto('orders.json')
where 
list_sum(list_transform(order_details,x -> x.price * x.quantity)) > 200 and
array_length(list_filter(order_details, x -> x.product.category = 'Electronics')) > 0;

使用了三個特殊函數(shù)，兩個顯式 lambda 函數(shù)，與一般 SQL 相比，這個代碼復(fù)雜、難理解。

Phython 展開方式的代碼也很長，這里只給出 lambda 的寫法：

ini
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders = pd.read_json('orders.json')
final_orders = orders[
    orders['order_details'].apply(
        lambda x: any(item['product']['category'] == 'Electronics' for item in x) and 
                  sum(item['price'] * item['quantity'] for item in x) >= 200
    )]
result = final_orders[['order_id', 'order_date']]

apply 一個大循環(huán)加顯式的 lambda 語法，套了兩個小的 for 循環(huán)，還要定義循環(huán)變量，代碼理解起來也費勁。

SPL 使用基本運算函數(shù)，不用顯式的 lambda 語法，也不必寫顯式循環(huán)，代碼最簡潔、易懂：

less
體驗AI代碼助手
代碼解讀
復(fù)制代碼
orders=json(file("orders.json").read())
orders.select(order_details.select@1(product.category=="Electronics") && order_details.sum(price*quantity)>200).new(order_id,order_date)

小結(jié)一下，SQL 天生只有平面表，無法實現(xiàn)嵌套結(jié)構(gòu)。DuckDB 補(bǔ)上了嵌套結(jié)構(gòu)，但如果展開嵌套結(jié)構(gòu)再計算的話，還不如直接使用普通平面表。直接對著嵌套結(jié)構(gòu)計算時，各層運算不一致，代碼讀寫都麻煩。且多行嵌套結(jié)構(gòu)要用特殊函數(shù)和顯式 lambda 語法，代碼啰嗦。Python 支持嵌套結(jié)構(gòu)，但也存在和 DuckDB 類似的問題。SPL 表的字段可以是表，嵌套結(jié)構(gòu)從上到下的數(shù)據(jù)組織都一致，都是對象，引用方法也一致，計算代碼簡潔、易懂，是最專業(yè)的多層嵌套結(jié)構(gòu)計算語言。

點擊下載免費的esProcSPL試用一下吧~~