Databricks 上的 SQL 编程.pdf

1、2024 Databricks Inc.All rights reservedSQL SQL PROGRAMMING IN PROGRAMMING IN DATABRICKSDATABRICKSSerge Rielau&Milan StefanovicSerge Rielau&Milan StefanovicJune 2024June 202412024 Databricks Inc.All rights reserved Sorting Sorting-your wayyour waySorting points by distance using LAMBDA and SQL UDFSor

2、ting strings,properly(Sneak peek!)Short handsShort handsGROUP BY,ORDER BYSELECT*-unleashed VariablesVariablesSQL Session VariablesWhat about identifiers?ScriptingScriptingEXECUTE IMMEDIATESQL/PSM:Its like SQL,but scripted(Sneak peek!)2AgendaAgenda2024 Databricks Inc.All rights reserved Task“Sort an

3、array of points by distance from(0,0)”Need a custom sort orderarray_sort()for sortinglambda function for the math Hide complexity in a SQL UDF3Sorting Sorting-Your wayYour wayQuicksort and custom sort expressionsQuicksort and custom sort expressions2024 Databricks Inc.All rights reserved Can be pass

4、ed to a number of builtin map/array functions Operates on each element,value of the map/arrayp-expr(p)(p,.)-expr(p,.)p:One or more identifiers,as required by the host function.expr(p,.):A simple(no subqueries,or SQL UDF)expression using p.Result must comply with expectations of the host function.4LA

5、MBDA functionsLAMBDA functionsAnonymous function with one or more named parameters2024 Databricks Inc.All rights reserved SELECTSELECT array_sort(array(5,2,8,1,3),(a,b)-a-b)ASAS sorted;=1,2,3,5,8Sorting Distancesd=sqrt(x*x+y*y)d1 d2 x1*x1+y1*y1 0=a blambda(a,b)0=a bdxy2024 Databricks Inc.All rights

6、reserved SELECTSELECT array_sort(points,(p1,p2)-(p1.x*p1.x+p1.y*p1.y)-(p2.x*p2.x+p2.y*p2.y)ASAS pointsFROMFROM point_arrays;=,6Sorting by distanceSorting by distancelambda(p1,p2)0 (x1*x1+y1*y1)(x2*x2+y2*y2)lambda(p1,p2)0 (x1*x1+y1*y1)(x2*x2+y2*y2)dxy2024 Databricks Inc.All rights reserved Stored in