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

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

css
體驗(yàn)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"
        }
     }
...]

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

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

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

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

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

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

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

scss
體驗(yàn)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;

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

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

csharp
體驗(yàn)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

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

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

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

語(yǔ)法完全不一致。

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

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

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

對(duì)比一下計(jì)算總運(yùn)費(fèi)的代碼：

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

語(yǔ)法完全一致！

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

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

ini
體驗(yàn)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 的字段多了個(gè)函數(shù) str，不如 DuckDB 和 SPL 簡(jiǎn)潔。而且 str 函數(shù)僅支持可以轉(zhuǎn)為字符串的簡(jiǎn)單類型，不支持多行的嵌套字段。

Python 也可以把多行的嵌套字段展開(kāi)計(jì)算：

ini
體驗(yàn)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']]

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

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

less
體驗(yàn)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 語(yǔ)法，要寫(xiě) lambda 關(guān)鍵字，還要定義參數(shù)，有點(diǎn)啰嗦。而且 lambda 函數(shù)中還要寫(xiě)顯式的 for 循環(huán)，相當(dāng)于兩重循環(huán)，會(huì)增加理解的難度。這里的 for 雖然是簡(jiǎn)化寫(xiě)法，也還是要定義循環(huán)變量 item，還是啰嗦。

我們也對(duì)比一下計(jì)算運(yùn)費(fèi)的代碼：

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

語(yǔ)法也是完全不一致。

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

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

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

csharp
體驗(yàn)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;

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

Phython 展開(kāi)方式的代碼也很長(zhǎng)，這里只給出 lambda 的寫(xiě)法：

ini
體驗(yàn)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 一個(gè)大循環(huán)加顯式的 lambda 語(yǔ)法，套了兩個(gè)小的 for 循環(huán)，還要定義循環(huán)變量，代碼理解起來(lái)也費(fèi)勁。

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

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

點(diǎn)擊下載免費(fèi)的esProcSPL試用一下吧~~