400 186 1886
業(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試用一下吧~~
