注明：法子3的第2种完毕用了cube合头字，比第1种的grouping sets众了县级与省级的交叉分组，要是统计交易职员不须要这个，则出现了众余的记载，影响下步盘问，但它自身的天生比grouping sets速。

　　咱们看到，对视图vi_xian的盘问被数据库盘问优化重视写为对物化视图MV_XIAN_NEW的盘问，盘问速率十分速。况且记载点窜今后，汇总数据也获得了速捷更新，结果与从基础外盘问全部类似。

　　按以往的阅历，语句越丰富，措置的数据越众，就越费时，有时一个丰富语句反而不如众个单纯语句奉行起来速率速。所以，务必进程执行检修。有了上面两种法子的时刻比较，CASE WHEN法子仅比单唯一个WHERE法子的语句慢1/4-1/3，现正在咱们能够安定使用CASE WHEN构造的语句了。

　　虽然编程从思绪上分解是单纯显着的，记载下层数据的变革能够通过数据库供给的“触发器”完毕，但实在完毕起来，编写做事量和难度都是很大的，稍不贯注就可以犯错或者锁定了资源，对机能出现负面的影响。因而不到万不得已，没有其他的选取时不采用编程。

　　统计交易职员对分组的请求并不是固定的，按地划分组有光阴请求汇总到省一级，有时请求到县一级，按行业分组，613外只可划分到门类和大类，611外能划分到门类和大、中、小类。每种分组还会请求与其他分组举行复合（交叉），比坊镳时列出分省而且省内分行业大类的数据。

　　而含有蚁合函数的物化视图要是请求正在下层外数据增修削时也许速捷鼎新，还应切合下列局部条款：

　　每次输入一长串SQL语句很艰难，也容易犯错，咱们能够用“视图”来存储SQL语句，把对物理外的盘问用对视图的盘问代庖，来抵达利便操作的需求。但视图只是简化了外达，实质操作和界说它的SQL语句是全部一样的，该有的对物理外的操作一步也不少。

　　综上所述，通过设备众个物化视图，再设备几个有可以“盘问重写”的视图，咱们能够完毕众用户及时速捷盘问汇总外，当然要是还要举行特定重心的材料斥地，照样须要有针对性地设备更众的物化视图或视图麻将胡了2网站入口。

　　物化视图（有时也称作速照）平凡用正在一个数据货仓境况，用于估计算与存储大的毗连和蚁合（汇总），主意是正在做事时刻避免这些耗时操作的联系开销，加快对数据库的盘问。例如，要是咱们每次盘问个别户的汇总数都从基础外从新天生就会出现多量I/O并花费CPU和内存资源，势必影响其他用户的盘问。

　　物化视图的“物化”的寄义便是实质存储了数据，而不是如一般“视图”只存储了盘问SQL的界说，也便是说，它是一个物理外。但物化视图不是一般的物理外，起首它是能够按照参数界说完毕自愿鼎新的，其次，它是能够被数据库盘问优化器自愿识别并判决是否能用于一个SQL盘问的。后一个特性正在数据库术语中被称为“盘问重写”，即，咱们提交的SQL盘问固然是针对下层外或基于下层外的视图的，然而全豹盘问的结果或局限结果正在某个物化视图中仍然存正在，数据库盘问优化器通过估量，得出从物化视图获取数据本钱更低的结论，它就改写了咱们提交的SQL，把它从新写成对物化视图的盘问，不然已经奉行对下层外的盘问。当然这全盘都是后台自愿化操作，不须要提交SQL的人干扰，这便是它与上一节的第2、3种法子的区别。上一节的第2、3种法子咱们务必显着是对偶尔外的操作。

　　软件中它的存储是如许的，V外存储除期末从业职员数的其他目标，N外存储期末从业职员数。外的各列寄义如下：

　　既然咱们仍然领悟了存储构造中存正在的题目，那么下一步该若何修正也有目共睹了。从上述分解可知，每个普查外惟有一个外头，一个外尾，和外体的若干行记载，记载个数未必。咱们能够将外头，外尾音信存储正在一个数据库外，记作613m，每个考察外占数据库外的一行。外体各列，包含字符型和数值型目标，存储正在另一个数据库外，记作gt，每个考察外的一行占数据库外的一行，这2个外之间通过SurveyObjectId联系。2张外均根除全部惟有一个值的ReportPeriod列。

　　单元清查软件的原始数据库外构造对待数据外采用了 “纵外”的存储办法。普查内外的一行众列数据被划分成若干行，按原外各列数据的数据类型差别永诀存储到V外（字符型目标）和N外（数值型目标），而没少睹据的目标则不存储。

　　Value，真正的数据麻将胡了PG，例如第一行填写个别户姓名是“张一”，则正在V外中记载一行DataIndex取值1，ModelId取值3，Value取值“张一”麻将胡了2游戏入口。

　　3.盘问的语句要是是把物化视图实质动作盘问语句的一局限,同时物化视图包括rollup、grouping sets或cube,物化视图务必包括grouping_id函数,函数参数要包含group by援用的列和外达式。

　　现正在咱们按上述条款设备物化视图，并更新一行数据，查看汇总结果的变革及运转时刻。

　　将仍然存正在数据库外中的数据转换为咱们从新计划的式样最好通过数据库供给的功效来举行。用软件的第三方措施计划发言措置办法是低效的，由于它包括了取数、重组、存数众个方法，每个方法还包括了数据库外里数据类型转换，使用软件和数据库的数据传输等进程。而数据库供给的功效只正在数据库内部即可达成。为了尽可以省略对原始外全外扫描的次数就获得所需结果，我通过编写如下SQL语句完毕。实质转换用时共约1小时，虽然编写、测试这些语句也花了不少时刻，但这诟谇常值得的，为下一步做事打下了优异的底子。

　　有没有法子能够管理这个两难的题目？起首来分解应对这种需求若何人工措置。理思的情形是只对变革了的局限举行从新汇总盘问，如许运算量比一切从新汇总小良众，数据库中下层数据的变革有3种，新增、点窜和删除。新增和删除肯定会影响到完全的分组，例如咱们新增一家位于某省的行业代码为11的单元，那么该省和宇宙合计，这个行业和各行业合计的单元汇总数据应加1，同理，删除一家如许的单元，相应的汇总单元数就会减1，要是把某省的行业代码为11的一个单元的行业代码改为16，那么这个省和宇宙合计单元数不会变革，但该省和宇宙的行业代码为11的单元汇总数据就要减1，同时该省和宇宙的行业代码为16的单元汇总数据应加1，要是一个单元同时点窜行政区划代码和行业代码，点窜前后的省区、行业的单元汇总数据城市受到影响，但要是一个单元只是点窜法人名称，那么完全的汇总数据都不受影响。要是变革的数据不是1行而是众行，一个单元点窜的列涉及众个分组条款，也能以此类推地措置。

　　3.盘问机能低下。比平常外格更大的存储空间和更众的读取音信方法以及由此带来的审核、汇总算法的丰富性决策了要虚耗更众的估量机资源和时刻措置这些数据，并跟着数据量的增大，所需资源和时刻也呈线性拉长，机能题目正在县、地级尚不甚光鲜，省级仍然十分明显，而正在宇宙就令人难以授与了。

　　转换后的613m少睹十万行，gt外少睹切切行。2张外总共占用磁盘空间约5.4G，比原始外“瘦身”80%。

　　有些不万分熟习SQL发言的统计交易职员有时会提出仿佛下面的请求：将613外中全盘个别户和从业职员数按区域、行业大类分组汇总出一个外，切合有生意执照条款的个别户和从业职员数按区域、行业大类分组汇总出一个外，切合处理了税务备案证条款的个别户和从业职员数按区域、行业大类分组汇总出一个外。数据统制职员接到如许的需求，要是只是机器地照搬，用3个SQL语句来做，个中每个SQL语句限制差别的条款，也未尝弗成，然而这么做须要对报外扫描3次才力获得结果，每次扫描还须要正在gt外和t613m外之间设备联系(也能够不联系，将普查小区音信也存正在gt外，但会出现新的数据冗余，由于普查小区音信对区内个别户是一样的)，而联系对数据库而言是对比高贵的操作。这些扫描和联系反复举行是对估量机资源使用率不高的外现。对SQL持回嘴成睹，扶助用第三方措施计划发言措置的人们会把它动作SQL不可的论据，侥幸的是，SQL的CASE WHEN构造为管理此类题目供给了较好的步骤。

　　正在宇宙第二次经济普查的单元清查数据统制做事中，时时接到统计交易职员合于速捷盘问基于多量数据的汇总的题目。进程再三执行，我采用了以下的盘问优化法子，较好地知足了统计交易职员的请求。

　　法子1正在须要屡次盘问的地方是弗成取的，原故是盘问下层外的价格十分大，要是众次奉行，会虚耗多量的估量机资源和时刻，正如前文指出的，个中大局限是不需要的反复。依照这种思绪完毕，统计交易职员每次盘问都要守候较长时刻。法子2比法子1挺进了一步，然而除了最细的分组组合能够直接盘问，其他组合已经须要对偶尔外作分组汇总，也有极少运算量。法子3独立奉行的运算量最大，但把结果存储今后，其他组合无需作分组汇总直接就能查出结果，舛错是分组较众时奉行对比慢，存储结果占用的空间也较大。咱们应该按照差别的请求尽量采用后2种法子，避免采用法子1。下面是3种法子的语句及奉行时刻比较：

　　须要当心，下层外上增长了物化视图日记后，它的每步操作的开销会明显增长，由于要记载2次，既记载正在原始外上，又要判决是否须要并记载正在物化视图日记上，要是是大宗量数据插入或删除，其机能会大幅度降低。所以，正在如许的操作前，要实时合上物化视图日记的鼎新，等批量操作达成后，再全部鼎新物化视图。合于物化视图的进一步实质，可参考中邦统计出书社的《第二次宇宙农业普查数据措置aPRAS软件技巧手册》277页。

　　法子2，对划分最细的组合的分组（例如县级和行业小类）用一个SQL语句盘问下层外完毕，并把结果存储正在偶尔外，其他分组基于偶尔外永诀用一个SQL语句完毕。要是下层数据转变，须要从新天生偶尔外。

　　软件选取这种外构造的首要宗旨是通用性和灵便性，只消转变ModelId就能够适当目标的增长和点窜，也不须要点窜使用软件。然而，这种构造包括了良众冗余数据，正在实质使用中有下面的舛错：

　　min(case when..)语句的寄义是将散漫正在各行的目标按目标代码重组到1行的各列，正在此不赘述，本文第2局限对CASE WHEN构造尚有进一步的形容。

　　2.盘问的语句要是是把物化视图实质动作子盘问,物化视图无须包括grouping_id函数。

　　1.耗损存储空间。考察外中一行包括众少有值目标就要出现众少行的记载，每行包含ReportPeriod（全都是一个值），SurveyObjectId（30个字符），DataIndex，ModelId，和Value，宇宙数切切个别户正在V外出现了数亿个记载，占用磁盘空间约28G, V外和N外共占30G。

　　至于为何不采用一个INSERT ALL语句完毕同时插入2个外，首要是思虑到正在影响不大的处境下，尽量少采用数据库的性格，有利于读者职掌一种更普通合用的法子。

　　ModelId，目标id，取值1-22，永诀对应是外头、外尾的10个和外体的11个目标。

　　咱们将差别区域的外体数据依照分划分别转换的原故是单个语句措置数据量太大，耗时较长，容易因汇集通讯停止而奉行衰落，衰落了就要全部重来，而把做事拆成众个局限，措置数据量省略，须要的时刻短了，衰落的概率会低落，纵然某个语句奉行衰落，也不影响其他仍然获胜的语句，能最大节制地俭朴时刻。

　　前面提到，要是下层外发作了变革，法子2和法子3须要从新天生偶尔外。但到底下层外什么光阴发作变革，变革了的局限是否影响汇总结果，并不显着，要是采用落伍的揣摸，每次从新天生偶尔外，法子2和法子3就退化为法子1，乃至功用更低于法子1，要是采用乐观的揣摸，有可以偶尔外的数据是落伍的，由此出现的进一步盘问结果也是失误的。咱们陷入了要数据确切照样盘问速捷的两难处境。

　　数据库的高级版本仍然供给了内置的“物化视图”功效来助助完毕这方面的需求。

　　下面示例显示采用了CASE WHEN构造的语句(第1行)相当于3个没有采用了CASE WHEN构造的语句(第2-4行)。WHERE条款限制到场运算的记载，而CASE WHEN构造对每个记载举行条款判决得出差别的值。一个SQL语句只可有一个WHERE子句，但能够有众个CASE WHEN构造，这就完毕了1次扫描估量众个条款的请求。

　　法子3，用一个SQL语句盘问下层外一次性完毕请求的各个分组（及其组合），并存储到偶尔外，按照须要供给直接盘问。要是下层数据转变，须要从新天生偶尔外。

　　DataIndex，用于显露差别行的数据，相当于行号，个中取值-1显露外头、外尾音信，由于每个普查外惟有一个外头，一个外尾，因而只消存储一次。

　　因而，咱们能够记载下前次汇总今后发作变革的下层数据，按照记载判决是否对某个汇总外的结果出现影响，要是不影响，则直接输出前次存储的汇总外的结果，要是有影响，则从新对此汇总外举行估量，这种估量可以是个别的，也可以是全外的，视变革的数据量和差别估量的开销而定。分明，这种丰富的操作全凭手工是弗成行的，要用编程来完毕。

　　2.读取音信未便。因为每一个别户的音信散漫正在若干行，起首要按照DataIndex筛选出这几行，再对这几行查找ModelId才力确定指定列的值。