sql over开窗函数1.使用over子句与rows_number()以及聚合函数进行使用,可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。
2.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:“所有订单的总和”、“每一位客户的所有订单的总和”、”每一单的金额“
关键点:使用了sum() over() 这个开窗函数
如图:
ipaY4ZeU4KcO/eursKLujeuGXEU/8RnUe6QFr7VexWKHG3jnntvR+XmdnzNupfBQSOAJI0bkP5GfBOl/FxU1WtKkDO866vw+t17fevj0CQnunn3hj4kvwVVJnfM49x7nTZ3Tr4dLyrdezkes6RlI0N66TVarylBHuDe5ftoBTPnE+jd474JZrnABuG+eBqD/wDfckLT75R7B91zvs6W4f8e9gNmzlSbCEcGPSsLW2OGXjvpQB4wc16+j1Zw1wrc7Wi2mwtkeP/jUr67PRpNVEgfBo0bXniQ7gWTlQG/94LDcPcVaDZFDOUIB5D2bVI6K6NaGLfGHL2mAzndoYkht3SFF3ev4s0k1F0BGuB19zrUnQIa4HX3OtSdAhrgxe6tXNufU9cuE33+IjIJBWUeEEkjb+/eSdW27dRfMb9MQkGZB0TSyHt17+IzAd988610693bTqNJP3xBmYSCMg+IpI4Xu3f0Y8Nldq6uXSQyCQVlHhBJHa+7t14moaDMAyKp43X31sskFJR5QCR1vBL3DlpYLHPv91Pz9F3yyvfT7QGzoBQs0xdpmofjWfjJqb0IMVDn4wP8iO5/YaW7b0K1aam30AfOoxRgNc7tvYJGnlPqeHn3duvdpnNv1zLxQE2+cj4+RH/Ge8UulXxXvFem7RJMlw/5fop763x8ePoG70TfGL/Ff+BkEp2LcjhUZxU08pxSx6v13NvpfHwAwxmUCd+QfnJyL0Kce28fQn0Z+gL8lfD/L0OF/8DpJBnNFxyizpSuxUoaeU6p49XuXnCkQ2XAdCdsupx7xVMcdkJx2Tv6rrgjEUELz6/n48Plz/QHTqn8aIb1wnUmSzfXg9EK580KeXW7N2o5UCZsX9rhBXsR4qCuHYY7Cx2nL6XSF04PT08PoDpzj/bsaIY4RJ2jwg019Nczh1bHq9i91EGvcHZdsBchwcwZfYZk+oBrFk69Zp10lvfa9dW4iqkz1Kom0ep4tbo30TqrcC+6KCL/ln3R1diSD5lCfK8lakIcjPjJpRb3rpBXp3u/n6j5ZlAGNCo6OHKfnN6LUBKKnikFHUfdmYze6jAXmmqyvZbAibsmeqWokeeUOl6V7oU/n4XXgvRvwgV3rZJ7EWKgiJ+jqR+Gwi8iAImfoWe8TOR6LY0DDrHB76IQVt7Ic0odr8S9gzZ/1kqvzAMiqeN199bLJBSUeUAkdbzu3nqZhIIyD4ikjhe7d6L8DnXtIpFJKCjzgEjqeAP3DgjoyEhdu0hkEgrKPCCSOt7Avb755puurfVknDqZhIIyD4ikjhe7d/Rjg1/36pV5QCR1vO7eepmEgjIPiKSO191bL5NQUOYBkdTxZtz7cbg98dX/s7RTuHeEZJzpXuHbjpTtZJxO8LHdRLsl1+Wgsos8CorE8FLPRaKnIa/i+3pcOs6913+q9N9f4b9qW+XecZJxiIf+o1V1lWVybZdg6p5ht5uMcz4+wMMTbre4H28iAZdYAomU5CU6SxaTcD4+PH0XDaoiSWfO6N+VXvzc24kMq3g4ntGSpHgNl7RMWpx77SfjnI8P9ML03HpMCpDtiNkkmDnf6i47yER9vcAKwUnPvTfMSvfGBzPJmtiCMozYCYX1ZJzvp+bpmxqL2SQTAnCpBcxIeffGEwvmWoYgWeLc+/WyadC/Ar4O98bLoOPx3kbNWFSGEQeVCLI5G0nGufYFfSxKZRWF+0NAFe4NOkuw4vocpdVJBlWR8u4dYt1J3ctYt2UDLorKMBLMnNFQNJKM01VCcMjLTBYugCrc27ZtKkmDqrAkyGmoMu4daN3p3MtbFxXv/qwpk1Z2cDN/p8pH72Rn+BMqAYjvqvY3roia8e69mUHNdW/6PgtbbArrthn3HvdDctgndK8gjoQ4X9WVSSsJdRfJOG0LkQRZRXAvDLjue85xZ4FXyF8Bojt303Qa597+x96maZpmPfec4/ARPo6ks0BdGb7tUjz49z1jyTht24Y30xLtBrFXB4gk6dD4923i14Cwr4sGVUWF/VmrGpmEgjIPiKSO191bL5NQUOYBkdTxunvrZRIKyjwgkjpe7F5PxpHLJBSUeUAkdbyBewdE32Skrl0kMgkFZR4QSR1v4F7ffPNN19Z6Mk6dTEJBmQdEUseL3Tv6scGve/XKPCCSOl53b71MQkGZB0RSx+vurZdJKCjzgEjqeDPu7R6WrFvcK3avJ+NgVPQW+sB5lB3N95OMk2h/ko4IzVkgGefnuL8tMKr/R0A9GceTcb7RjuqScYj2T9NxkQwj0wlnzp9v283baUXn3k7JFVuejDNYbK+d7ygZJ9n+VGXjcTMZncy9p+fdVOfeFJQn47RgWWj6A6cU12v3lYyTav+Ee8N583R0efd+vWxWeN17EbNGn2myojKMOKizJ+MEJeP9IeDq3Ztq//yxadJwBfnMeXWpdHy8BhNw4ck4QQUAu0oIDnkmknHKOhTvt7h7V5cIy1sXFfdknDolAPEd9P42DlEzC8k46fYXuXeZ695Z0thTUJ6M48k4c6uwQ9sMXbfbUvecu9976068E7nXk3Hmkdy995OMk/41m6Dr2mGJ33vH2PxZK70yD4ikjtfdWy+TUFDmAZHU8bp762USCso8IJI6XuxeT8aRyyQUlHlAJHW8gXsHRN9kpK5dJDIJBWUeEEkdb+Be33y7561pGnX/bT0Zp04moaDMAyL9/Pv7P1Vqmqb1u1Z1MgkFZR4QqWmapf1YJnzudffKZRIKyjwgkp973b12ZB4Qyea59+OwriclW0/GmUVZ99pLxkn55Peva+1+/U6+Ehfu3mMKD5Hg3Ht63k34nHOcMJJ/JX7En3joP3o0vLIM29lppu4xfGr5+pMn46wxGSd57v39q9m+/rn+9es3+UqnP6/b61u3v5jCw5Q9936+bTePh8rlgdOdezuRC8Q9GWcUsb12NpmMk/NLb83kK8Chlz9hiT+v2xHtmzv3HvfN4b16ce/07o3PkJI1sQVlGLETCk/GAeXifVeZjJO/7v3zuo3PtOEryKvb1z/g3Pvnddsg9w8Rf+69Rkmu1r3MGn1mQBSVYcRBnT0ZJygZ7w8BV+Je/tz753WLLlzjV2j39te921+/8Ll7iLhzb2fadbqXj9dgAi48GUeoFGBXCcEhT1MyTv7cKzj5ku7t1J+GxxBz7v182wb3ST0ZByk7uJm/U+WjdzwZZz4JrnsFF77RdW9YdszbVoJ7zis893oyzizK9Zq1ZJzUuRd47vpn/Arl5utffYnfv8b9zYi/7l2pez0ZZx7J3WsmGSflk+4H2+40G73Sn2jX9HvvsM2ftdIr84BIqXPvaiU697p7SZmEgjIPiCS57l2V/NxbL5NQUOYBkdSfez0ZRy6TUFDmAZF0n3sHRN9kZHIcmISCMg+IpPvc65tvd741Kwi7Kfpv68k4dTIJBWUeEEkd74/fc66WSSgo84BI6njdvfUyCQVlHhBJHa+7t14moaDMAyKp4+Xd+/7YP/22eTtN515PxsGo6C1PxplBDG9UYfhsKA5XiPv6tsvIbFn37j+mPfd6Mo4n46xllUKSl+gsmRX7bJRJjkyse0/Pu+3z1xwzZ0/Gof/fk3HmlGDmfKu77CAT9vXM597T8+52+l/VGiMgT8aZUFyvWUzGybs3nlgw1zKYZGb3HvfN9dz7+bZtHo+rcy+zRl+SeiMpw4iDSgTZnD0Z5/YqBFTh3qCzBCuuUV/Pf93bb9VLfD0Zx5NxvtNfsapkHNnMWXiFJb30GyKt7uWti4p7Mk6dEoD4rqqlZJxsmeR9llyxud379bJpDu8///7+/Ht/XNUvRp6MM4uyo7lHMpGMwxyO079xkL8CkBSzn3s/Dmu8a+XJOPNI7l4zyTipt6LO4rovfbG/0My5evNnrfTKPCCSOl53b71MQkGZB0RSx+vurZdJKCjzgEjqeLF7PRlHLpNQUOYBkdTxBu4dEH2Tkbp2kcgkFJR5QCR1vIF7ffPNN11b68k4dTIJBWUeEEkdL3bv6McGv+7VK/OASOp43b31MgkFZR4QSR2vu7deJqGgzAMiqePNufe4vz4Pdn3geSL3ejIORkVvoQ+cR9nRfMfJOFz3dQX6zsqWrhLv3vfH5rqs9+Mw0b++HYeG5F9hnxq//Rk9+F5ZJtd2CabuMXxPxmFW56tNxmG67yLQOHANx7j9x7q3X50/9czZk3Ho/z97Ms6MEsyc+6M8RoiK9Y0z2SGXcy9YIbha98ZnSMma2IIyjNgJhSfjgHLxvtqTcYg+AgobZ7pVnRn37g773Sqvey9i1ugzA6KoDCMO6uzJOEHJeH8IqMK9qLOIPurfCRrneiKe+br362Wz5lwrPl6DCbjwZByhUoDBuLz7ZBw0L44bBzTLyKfhzHVvd6fKk3FiZQc383eqfPROdoY/oRKA+A56NEahzCfjIENSjQNudYzbfax7+zT2lZ17PRlnFmVHc490t8k46QFD3Wqc89z7b6W/93oyzjySu/eek3FSk4bgOLTE770jbP6slV6ZB0RSx+vurZdJKCjzgEjqeN299TIJBWUeEEkdL3avJ+PIZRIKyjwgkjrewL0Dom8yUtcuEpmEgjIPiKSON3Cvb775pmtrPRmnTiahoMwDIqnjxe4d/djg1716ZR4QSR2vu7deJqGgzAMiqeN199bLJBSUeUAkdbyce79eNsHzb9dnnqdwryfjYFT0FvrAeZQdzXeSjMN3X1zhuLMmohOfe0/Pu6pHnT0Zx5NxvtGO6pJxoM7Hh6dvbpzEnTUZndS9H4c1/evbQOQCcWnqjSfjsGJ77XyXyThx93H9d2WZjE7m3tPzrjbgypNxcO08GUd1Mg5h3lQfEwkkc67v7S+A6xb3Tu1eZo0+02RFZRhxUGdPxglKxvtDQC3uPUcBdPQ4CTtrWfd2a/TX5V7Gui0bcOHJOEKlALtKCA55lpJx8iPw+mp0M2Ux91Zn4kzqXt66qLgn49QpAYhvwfY3roia2UnGEc7OiM5a7rr3821beb9qQvd6Ms4skswk+5vHdL0sJOO0bUvcjEv1Q9xZi91zPu4HRjp7Mk4KyJNxFgdE4njD7qN4qfEJTzcT0Emuewdt/qyVXpkHRFLH6+6tl0koKPOASOp43b31MgkFZR4QSR0vdq8n48hlEgrKPCCSOt7AvQOibzJS1y4SmYSCMg+IpI43cK9vvvmma2s9GadOJqGgzAMiqePF7h392ODXvXplHhBJHa+7t14moaDMAyKp43X31sskFJR5QCR1vBn3duE41QsVPBmnjZ/rD6NV0isEiXiROR8Czo7mO0nG6STgTbzFdPbgCifce3reXdcGvj82E2VrdMMyHqjJV+JH/ImH/qmFWjVlcm2XYAqzUdC7T56MEwGufZVCwMtVmHirX9Mwbv/l3HuL1Pg4rOlf3wYiF4hnl2UVlEmLc2+88qDHoOtL/v/Zk3FmVAEvU+H4rckOufzMuVseWL9A35NxcO2INIrUubdbQ5r+wCnF9dq9JeOEvEyF47emW9WZue79OT3vmqapnTZP7V5mjT7fvp6MI1Ea8N6ScTBvqXsfnp5SlxHDK5ydOdev0fdkHDrLI6x1qk89GWdOyxmlz73ThCtw7oWZONX5OJ6Mkx2X6fEqmeFPqAQgvoPe37giamYgGYfiLbzunaj72HPvcd/059413bXyZJxZlOs1gGQ/Gadt6WoK7zn3p4aZzr0///5+HG4HnNp8HE/GSQF5Ms7igEgF7iUqzCXjLPF77xibP2ulV+YBkdTxunvrZRIKyjwgkjped2+9TEJBmQdEUseL3evJOHKZhIIyD4ikjjdw74Dom4zUtYtEJqGgzAMiqeMN3Oubb77p2lpPxqmTSSgo84BI6nixe0c/Nvh1r16ZB0RSx+vurZdJKCjzgEjqeN299TIJBWUeEEkdb8a93ZOSdQ85i93ryTgYFb2FPnAeZUfzPSfj3F4ooxsXjnVvv0phutX53bCMB2rylfgRf+Kh/+hh8coyubZLMHWP4VPL1588GScCXP8qBcBbRsf09RgVpt2LVgiuaI0RELlAPLOGq6hMWpx745UHPQZdX/L/z56MM6PKeUV0BO9IEp57P9+2lbGSnoyDa0ekUaTOvd2a2fQHTimu1+47GSd4NS6Juo/o0DErnL3u3Twe1rU6/yJmjT6ffuDJOBKlAe89GQe9HpdG3Ud06HgVzt9z/jis7tzLx2swAReejCNUCrCrhOCQZzMZ5yYRHf/KEAndu7JsjZx1UfEg2qC0TFrZzmb+TpWP3snO8CdUAhDfQe9vXBE148f37ci16utemrdtWykd98ogce79etlcIzWO+2ZVibCejDOLcr0GkO4tGecqER3BO5KE173r+r03zmGpTr2RlOHbLsUDP5YE6sp4Mk67ECDSMPeK6Gb8vXeMzZ+10ivzgEjqeN299TIJBWUeEEkdr7u3XiahoMwDIqnjxe71ZBy5TEJBmQdEUscbuHdA9E1G6tpFIpNQUOYBkdTxBu71zTffdG2tJ+PUySQUlHlAJHW82L2jHxv8ulevzAMiqeN199bLJBSUeUAkdbzu3nqZhIIyD4ikjlfi3iBYo/RfFfRknDZ+zv32ZH7qe+K30AfOo+xo/vZkHK73rt1F9v6IFU659/Nte/m+m3tPz7t+vf7m7TTcvXHCSP6V+BF/4qH/6MH3yjK5tkswdY+lezIOs34dAK5/lQLg5ej4hUejhmsUnnuP++6U+3EQLV3wZJwIw5NxLCfj8OMm6v1BKnMvSrqSrNf3ZBxcO0/GsZ2MAybKRLTKqGy63cus0WcGRFEZRhzU2ZNxgpLx/hBw9e4tScZJL8Yeddbctqrdy8drMAEXnowjVAqwq4TgkHfXyTjZi4iB0nrdy1sXFfdknDolAPE9VE/GSWnqjit07/j3nC/yZJygUjhIxZNxZlEB71UsXfADwSRche5dx++9nowzj+Sj2ZNxSLq+8DQzCol7B23+rJVemQdEUsfr7q2XSSgo84BI6njdvfUyCQVlHhBJHS92ryfjyGUSCso8IJI63sC9A6JvMlLXLhKZhIIyD4ikjjdwr2+++aZraz0Zp04moaDMAyKp48XuHf3Y4Ne9emUeEEkdr7u3XiahoMwDIqnjdffWyyQUlHlAJHW8EvcGT0omXhno3vpkHPjoM3pQT5DVIt8r2Xakugfb+yf4A12fzE99z/qTcVANeUBmDWyHw7TGbCoa