直接case when 聚合和先聚合后case when在duckdb150和sqlite3.52的性能比较

同样对10000000行的内存表执行2个值分别和7个值和11个值的复合分组聚合。

C:\d>duckdb150 DuckDB v1.5.0(Variegata)Enter".help"forusagehints.memory D.timeronmemory Dcreatetabletasselecti%2a,i%7b,i%11cfromrange(1,10000000)t(i);RunTime(s):real0.288user0.234375sys0.046875memory Dselecta,count(casebwhen0then1end)b0,count(casebwhen1then1end)b1,count(casebwhen2then1end)b2,count(casebwhen3then1end)b3,count(casebwhen4then1end)b4,count(casebwhen5then1end)b5,count(casebwhen6then1end)b5fromtgroupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │ b0 │ b1 │ b2 │ b3 │ b4 │ b5 │ b5 │ │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│714285│714286│714286│714285│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.051user0.171875sys0.203125memory Dwitht1as(selectcount(*)cnt,a,bfromtgroupbya,b)selecta,sum(casebwhen0thencntend)b0,sum(casebwhen1thencntend)b1,sum(casebwhen2thencntend)b2,sum(casebwhen3thencntend)b3,sum(casebwhen4thencntend)b4,sum(casebwhen5thencntend)b5,sum(casebwhen6thencntend)b5fromt1groupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │ b0 │ b1 │ b2 │ b3 │ b4 │ b5 │ b5 │ │ int64 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│714285│714286│714286│714285│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.021user0.156250sys0.031250memory Dselecta,count(casecwhen0then1end)c0,count(casecwhen1then1end)c1,count(casecwhen2then1end)c2,count(casecwhen3then1end)c3,count(casecwhen4then1end)c4,count(casecwhen5then1end)c5,count(casecwhen6then1end)c6,count(casecwhen7then1end)c7,count(casecwhen8then1end)c8,count(casecwhen9then1end)c9,count(casecwhen10then1end)c10fromtgroupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │ c0 │ c1 │ c2 │ c3 │ c4 │ c5 │ c6 │ c7 │ c8 │ c9 │ c10 │ │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│454545│ │1│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│454545│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.061user0.406250sys0.093750memory Dwitht1as(selectcount(*)cnt,a,cfromtgroupbya,c)selecta,sum(casecwhen0thencntend)c0,sum(casecwhen1thencntend)c1,sum(casecwhen2thencntend)c2,sum(casecwhen3thencntend)c3,sum(casecwhen4thencntend)c4,sum(casecwhen5thencntend)c5,sum(casecwhen6thencntend)c6,sum(casecwhen7thencntend)c7,sum(casecwhen8thencntend)c8,sum(casecwhen9thencntend)c9,sum(casecwhen10thencntend)c10fromt1groupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │ c0 │ c1 │ c2 │ c3 │ c4 │ c5 │ c6 │ c7 │ c8 │ c9 │ c10 │ │ int64 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│454545│ │1│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│454545│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.024user0.015625sys0.000000memory Dpivot(selectcount(*)cnt,a,bfromtgroupbya,b)onbusingsum(cnt);┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │0│1│2│3│4│5│6│ │ int64 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│714285│714286│714286│714285│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.037user0.000000sys0.000000memory Dpivot(selectcount(*)cnt,a,cfromtgroupbya,c)oncusingsum(cnt);┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │0│1│10│2│3│4│5│6│7│8│9│ │ int64 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ int128 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│454545│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│ │1│454545│454546│454545│454545│454546│454545│454546│454545│454546│454545│454546│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.031user0.171875sys0.015625memory Dpivottonbusingcount(*)groupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │0│1│2│3│4│5│6│ │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│714285│714286│714286│714285│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.109user1.015625sys0.125000memory Dpivottoncusingcount(*)groupbya;┌───────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐ │ a │0│1│10│2│3│4│5│6│7│8│9│ │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ int64 │ ├───────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤ │0│454545│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│ │1│454545│454546│454545│454545│454546│454545│454546│454545│454546│454545│454546│ └───────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘ RunTime(s):real0.115user1.421875sys0.015625

DuckDB无论case when 7次和11次，都是先聚合更快。pivot比等价的手工case when慢一些。

SQLite version3.52.02026-03-0616:01:44Enter".help"forusagehints.Connectedtoa transientin-memorydatabase.Use".open FILENAME"toreopenona persistentdatabase.sqlite>.timeronsqlite>createtabletasselecti%2a,i%7b,i%11cfrom(selectvalueifromgenerate_series(1,10000000));RunTime:real0.803898user0.781250sys0.015625sqlite>selecta,...>count(casebwhen0then1end)b0,...>count(casebwhen1then1end)b1,...>count(casebwhen2then1end)b2,...>count(casebwhen3then1end)b3,...>count(casebwhen4then1end)b4,...>count(casebwhen5then1end)b5,...>count(casebwhen6then1end)b5...>fromtgroupbya;╭───┬────────┬────────┬────────┬────────┬────────┬────────┬────────╮ │ a │ b0 │ b1 │ b2 │ b3 │ b4 │ b5 │ b5 │ ╞═══╪════════╪════════╪════════╪════════╪════════╪════════╪════════╡ │0│714285│714286│714286│714286│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ ╰───┴────────┴────────┴────────┴────────┴────────┴────────┴────────╯ RunTime:real2.634213user2.515625sys0.109375sqlite>witht1as(selectcount(*)cnt,a,bfromtgroupbya,b)...>selecta,...>sum(casebwhen0thencntend)b0,...>sum(casebwhen1thencntend)b1,...>sum(casebwhen2thencntend)b2,...>sum(casebwhen3thencntend)b3,...>sum(casebwhen4thencntend)b4,...>sum(casebwhen5thencntend)b5,...>sum(casebwhen6thencntend)b5...>fromt1groupbya;╭───┬────────┬────────┬────────┬────────┬────────┬────────┬────────╮ │ a │ b0 │ b1 │ b2 │ b3 │ b4 │ b5 │ b5 │ ╞═══╪════════╪════════╪════════╪════════╪════════╪════════╪════════╡ │0│714285│714286│714286│714286│714286│714285│714286│ │1│714286│714286│714286│714286│714285│714286│714285│ ╰───┴────────┴────────┴────────┴────────┴────────┴────────┴────────╯ RunTime:real3.167292user3.031250sys0.125000sqlite>selecta,...>count(casecwhen0then1end)c0,...>count(casecwhen1then1end)c1,...>count(casecwhen2then1end)c2,...>count(casecwhen3then1end)c3,...>count(casecwhen4then1end)c4,...>count(casecwhen5then1end)c5,...>count(casecwhen6then1end)c6,...>count(casecwhen7then1end)c7,...>count(casecwhen8then1end)c8,...>count(casecwhen9then1end)c9,...>count(casecwhen10then1end)c10...>fromtgroupbya;╭───┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────╮ │ a │ c0 │ c1 │ c2 │ c3 │ c4 │ c5 │ c6 │ c7 │ c8 │ c9 │ c10 │ ╞═══╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╡ │0│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│ │1│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│454545│ ╰───┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────╯ RunTime:real3.113160user2.890625sys0.218750sqlite>witht1as(selectcount(*)cnt,a,cfromtgroupbya,c)...>selecta,...>sum(casecwhen0thencntend)c0,...>sum(casecwhen1thencntend)c1,...>sum(casecwhen2thencntend)c2,...>sum(casecwhen3thencntend)c3,...>sum(casecwhen4thencntend)c4,...>sum(casecwhen5thencntend)c5,...>sum(casecwhen6thencntend)c6,...>sum(casecwhen7thencntend)c7,...>sum(casecwhen8thencntend)c8,...>sum(casecwhen9thencntend)c9,...>sum(casecwhen10thencntend)c10...>fromt1groupbya;╭───┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────╮ │ a │ c0 │ c1 │ c2 │ c3 │ c4 │ c5 │ c6 │ c7 │ c8 │ c9 │ c10 │ ╞═══╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╪════════╡ │0│454545│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│ │1│454545│454546│454545│454546│454545│454546│454545│454546│454545│454546│454545│ ╰───┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────╯ RunTime:real3.251369user3.046875sys0.171875sqlite>

SQLite无论case when 7次和11次，都是后聚合更快。11次的两种方法差距更小，预计更多项需要case when时会反转。