登录
当前位置： 首页 > 知识库问答 >
问题：

高级分组-提取最近的事务，但保持分组

曹臻
2023-03-14 
INNER JOIN (select fp.pol_num
                      ,MAX(fp.id_prem) id_prem
                 from bapu.dbo.fact_prem fp
                 group by fp.pol_num) max_trans_id
        on fp.pol_num = max_trans_id.pol_num
       and fp.id_prem = max_trans_id.id_prem

DECLARE @d_inforce date; SET @d_inforce = '2014-06-01'


select limit_group_list.limit_group as 'Limit Band'
      ,pol_dtl.product
      ,pol_dtl.sub_product
      ,SUM(COALESCE(pol_dtl.gross_premium,0)) as 'Gross Premium'
      ,COUNT(distinct pol_dtl.pol_num) as 'Policy Count'
      ,SUM(COALESCE(pol_dtl.limit,0)) as 'Limit'
from
(select pol_product.pol_num
      ,pol_product.product
      ,pol_product.sub_product
      ,pol_premcomm.gross_premium
      ,pol_premcomm.commission
      ,pol_premcomm.net_premium
      ,pol_limit.lmt_pol_s as limit
      ,pol_limit.Limit_Order
      ,pol_limit.Limit_Group
from
-- Pull product for each policy from the most recent transaction
(select fp.pol_num
       ,fp.product
       ,fp.sub_product
 from bapu.dbo.fact_prem fp
 INNER JOIN (select fp.pol_num
                   ,MAX(fp.id_prem) id_prem
             from bapu.dbo.fact_prem fp
             group by fp.pol_num) max_trans_id
    on fp.pol_num = max_trans_id.pol_num
   and fp.id_prem = max_trans_id.id_prem
 -- Inforce Logic
 where @d_inforce between fp.d_pol_eff and fp.d_pol_exp
   and fp.pol_num not in (select distinct pol_num from bapu.dbo.fact_prem where lower(trans) like 'canc%' and d_tran_eff <= @d_inforce)
) pol_product
LEFT OUTER JOIN
-- Pull premium, commission, and net_premium per policy number
(select prem_comm.pol_num as pol_num
       ,prem_comm.gross_premium as gross_premium
       ,prem_comm.commission as commission
       ,COALESCE(prem_comm.gross_premium,0) - COALESCE(prem_comm.commission,0) as net_premium
 from
 (select fp.pol_num
        ,SUM(CASE
               WHEN fp.amt_type = 'Premium' THEN fp.amt
               ELSE 0
             END) as gross_premium
        ,SUM(CASE
               WHEN fp.amt_type = 'Add_Ded' and fp.amt_desc = 'Commission' THEN fp.amt
               ELSE 0
             END) as commission
  from bapu.dbo.fact_prem fp
 group by fp.pol_num) prem_comm) pol_premcomm
ON pol_product.pol_num = pol_premcomm.pol_num
LEFT OUTER JOIN
-- Pull limit bands per policy
    (SELECT pol_lmt.pol_num
      ,pol_lmt.lmt_pol_s
      ,CASE 
        WHEN pol_lmt.lmt_pol_s >= 50000001 AND pol_lmt.lmt_pol_s <= 100000000 THEN 20
        WHEN pol_lmt.lmt_pol_s >= 40000001 AND pol_lmt.lmt_pol_s <=  50000000 THEN 19
        WHEN pol_lmt.lmt_pol_s >= 30000001 AND pol_lmt.lmt_pol_s <=  40000000 THEN 18
        WHEN pol_lmt.lmt_pol_s >= 20000001 AND pol_lmt.lmt_pol_s <=  30000000 THEN 17
        WHEN pol_lmt.lmt_pol_s >= 15000001 AND pol_lmt.lmt_pol_s <=  20000000 THEN 16
        WHEN pol_lmt.lmt_pol_s >= 14000001 AND pol_lmt.lmt_pol_s <=  15000000 THEN 15
        WHEN pol_lmt.lmt_pol_s >= 13000001 AND pol_lmt.lmt_pol_s <=  14000000 THEN 14
        WHEN pol_lmt.lmt_pol_s >= 12000001 AND pol_lmt.lmt_pol_s <=  13000000 THEN 13
        WHEN pol_lmt.lmt_pol_s >= 11000001 AND pol_lmt.lmt_pol_s <=  12000000 THEN 12
        WHEN pol_lmt.lmt_pol_s >= 10000001 AND pol_lmt.lmt_pol_s <=  11000000 THEN 11
        WHEN pol_lmt.lmt_pol_s >=  9000001 AND pol_lmt.lmt_pol_s <=  10000000 THEN 10
        WHEN pol_lmt.lmt_pol_s >=  8000001 AND pol_lmt.lmt_pol_s <=   9000000 THEN 9
        WHEN pol_lmt.lmt_pol_s >=  7000001 AND pol_lmt.lmt_pol_s <=   8000000 THEN 8
        WHEN pol_lmt.lmt_pol_s >=  6000001 AND pol_lmt.lmt_pol_s <=   7000000 THEN 7
        WHEN pol_lmt.lmt_pol_s >=  5000001 AND pol_lmt.lmt_pol_s <=   6000000 THEN 6
        WHEN pol_lmt.lmt_pol_s >=  4000001 AND pol_lmt.lmt_pol_s <=   5000000 THEN 5
        WHEN pol_lmt.lmt_pol_s >=  3000001 AND pol_lmt.lmt_pol_s <=   4000000 THEN 4
        WHEN pol_lmt.lmt_pol_s >=  2000001 AND pol_lmt.lmt_pol_s <=   3000000 THEN 3
        WHEN pol_lmt.lmt_pol_s >=  1000001 AND pol_lmt.lmt_pol_s <=   2000000 THEN 2
        WHEN pol_lmt.lmt_pol_s >=        1 AND pol_lmt.lmt_pol_s <=   1000000 THEN 1
        ELSE 0 
    END AS Limit_Order
      ,CASE 
        WHEN pol_lmt.lmt_pol_s >= 50000001 AND pol_lmt.lmt_pol_s <= 100000000 THEN '$50,000,001-$100,000,000'
        WHEN pol_lmt.lmt_pol_s >= 40000001 AND pol_lmt.lmt_pol_s <=  50000000 THEN '$40,000,001-$50,000,000'
        WHEN pol_lmt.lmt_pol_s >= 30000001 AND pol_lmt.lmt_pol_s <=  40000000 THEN '$30,000,001-$40,000,000'
        WHEN pol_lmt.lmt_pol_s >= 20000001 AND pol_lmt.lmt_pol_s <=  30000000 THEN '$20,000,001-$30,000,000'
        WHEN pol_lmt.lmt_pol_s >= 15000001 AND pol_lmt.lmt_pol_s <=  20000000 THEN '$15,000,001-$20,000,000'
        WHEN pol_lmt.lmt_pol_s >= 14000001 AND pol_lmt.lmt_pol_s <=  15000000 THEN '$14,000,001-$15,000,000'
        WHEN pol_lmt.lmt_pol_s >= 13000001 AND pol_lmt.lmt_pol_s <=  14000000 THEN '$13,000,001-$14,000,000'
        WHEN pol_lmt.lmt_pol_s >= 12000001 AND pol_lmt.lmt_pol_s <=  13000000 THEN '$12,000,001-$13,000,000'
        WHEN pol_lmt.lmt_pol_s >= 11000001 AND pol_lmt.lmt_pol_s <=  12000000 THEN '$11,000,001-$12,000,000'
        WHEN pol_lmt.lmt_pol_s >= 10000001 AND pol_lmt.lmt_pol_s <=  11000000 THEN '$10,000,001-$11,000,000'
        WHEN pol_lmt.lmt_pol_s >=  9000001 AND pol_lmt.lmt_pol_s <=  10000000 THEN '$9,000,001-$10,000,000'
        WHEN pol_lmt.lmt_pol_s >=  8000001 AND pol_lmt.lmt_pol_s <=   9000000 THEN '$8,000,001-$9,000,000'
        WHEN pol_lmt.lmt_pol_s >=  7000001 AND pol_lmt.lmt_pol_s <=   8000000 THEN '$7,000,001-$8,000,000'
        WHEN pol_lmt.lmt_pol_s >=  6000001 AND pol_lmt.lmt_pol_s <=   7000000 THEN '$6,000,001-$7,000,000'
        WHEN pol_lmt.lmt_pol_s >=  5000001 AND pol_lmt.lmt_pol_s <=   6000000 THEN '$5,000,001-$6,000,000'
        WHEN pol_lmt.lmt_pol_s >=  4000001 AND pol_lmt.lmt_pol_s <=   5000000 THEN '$4,000,001-$5,000,000'
        WHEN pol_lmt.lmt_pol_s >=  3000001 AND pol_lmt.lmt_pol_s <=   4000000 THEN '$3,000,001-$4,000,000'
        WHEN pol_lmt.lmt_pol_s >=  2000001 AND pol_lmt.lmt_pol_s <=   3000000 THEN '$2,000,001-$3,000,000'
        WHEN pol_lmt.lmt_pol_s >=  1000001 AND pol_lmt.lmt_pol_s <=   2000000 THEN '$1,000,001-$2,000,000'
        WHEN pol_lmt.lmt_pol_s >=        1 AND pol_lmt.lmt_pol_s <=   1000000 THEN '$0-$1,000,000'

        ELSE 'Unknown' 
      END AS Limit_Group
    FROM 
      (select fp.pol_num
             ,MAX(lmt_pol_s) lmt_pol_s
       from bapu.dbo.fact_prem fp
       where fp.amt_type = 'Premium'
       and fp.product in (
'Package',
'Package - Crime',
'Package - Fine Art',
'Package - Jewelers Block',
'Package - Specie')
       group by fp.pol_num) pol_lmt
       ) pol_limit

 ON pol_product.pol_num = pol_limit.pol_num
 where pol_product.product in (
'Package',
'Package - Crime',
'Package - Fine Art',
'Package - Jewelers Block',
'Package - Specie')
 and pol_product.sub_product in(
                        'Umbrella/Excess',
                        'Workers Compensation',
                        'General Liability',
                        'Commercial Auto',
                        'Commercial Property')
                        ) as pol_dtl
RIGHT OUTER JOIN
(select distinct fp.product 
               ,lmtgrp.limit_group
from bapu.dbo.fact_prem fp
cross join
(select '$50,000,001-$100,000,000' limit_group
 UNION
 select '$40,000,001-$50,000,000'
 UNION
 select '$30,000,001-$40,000,000'
  UNION
 select '$20,000,001-$30,000,000'
  UNION
 select '$15,000,001-$20,000,000'
 UNION
 select '$14,000,001-$15,000,000'
 UNION 
 select '$13,000,001-$14,000,000'
 UNION
 select '$12,000,001-$13,000,000'
 UNION
 select '$11,000,001-$12,000,000'
 UNION
 select '$10,000,001-$11,000,000'
 UNION
 select '$9,000,001-$10,000,000'
 UNION
 select '$8,000,001-$9,000,000'
 UNION
 select '$7,000,001-$8,000,000'
 UNION
 select '$6,000,001-$7,000,000'
 UNION
 select '$5,000,001-$6,000,000'
 UNION
 select '$4,000,001-$5,000,000'
 UNION
 select '$3,000,001-$4,000,000'
 UNION
 select '$2,000,001-$3,000,000'
 UNION
 select '$1,000,001-$2,000,000'
 UNION
 select '$0-$1,000,000'
 UNION
 select 'Unknown') lmtgrp
 where  fp.product in (
'Package',
'Package - Crime',
'Package - Fine Art',
'Package - Jewelers Block',
'Package - Specie')
  and fp.sub_product in(
                        'Umbrella/Excess',
                        'Workers Compensation',
                        'General Liability',
                        'Commercial Auto',
                        'Commercial Property')              
 ) limit_group_list
 on pol_dtl.product = limit_group_list.product
and pol_dtl.Limit_Group = limit_group_list.limit_group
group by pol_dtl.Limit_Group, pol_dtl.Limit_Order, pol_dtl.product, limit_group_list.limit_group, sub_product
order by pol_dtl.product, pol_dtl.Limit_Order

共有1个答案

邓深
2023-03-14

首先，我建议不要对表和子查询使用相同的别名(fp)这会使我们很难遵循：s

其次，您可以用下面的查询替换group/max查询以获取最近的行

INNER JOIN (select subfp.pol_num
                 , subfp.id_prem
                 , ROW_NUMBER() OVER(PARTITION BY subfp.pol_num ORDER BY subfp.id_prem DESC) AS priority
             from bapu.dbo.fact_prem subfp
             ) max_trans_id
    on fp.pol_num = max_trans_id.pol_num
   and fp.id_prem = max_trans_id.id_prem
   and max_trans_id.priority = 1

我希望这有帮助

 类似资料：
  • 事务分组与高可用

    最佳实践1：TC的异地多机房容灾 假定TC集群部署在两个机房：guangzhou机房（主）和shanghai机房（备）各两个实例 一整套微服务架构项目：projectA projectA内有微服务：serviceA、serviceB、serviceC 和 serviceD 其中，projectA所有微服务的事务分组tx-transaction-group设置为：projectA，projectA正

  • 适合父级并保持宽高比的分区

    我有一个保持宽高比的元素：它根据其宽度计算其高度（使用填充技巧）。我想做的是通过垂直和水平地拟合最大可用空间，将此放入另一个div中，不进行裁剪。我认为最接近我想要的东西是-这是而已。 我希望div覆盖最大的高度和宽度，同时保持纵横比。没有垂直或水平的作物。 它甚至可能与CSS只有？如果有，如何？ 更新：这是一篇很好的文章，介绍了目前的情况。 代码（可以是任何其他解决方案，不必在此代码段上构建）：

  • Seata 事务分组

    事务分组是什么？ 事务分组是seata的资源逻辑，类似于服务实例。在file.conf中的my_test_tx_group就是一个事务分组。 通过事务分组如何找到后端集群？ 首先程序中配置了事务分组（GlobalTransactionScanner 构造方法的txServiceGroup参数） 程序会通过用户配置的配置中心去寻找service.vgroupMapping .[事务分组配置项]，取得

  • 分级任务组织

    我在以可管理的方式组织构建时遇到了问题。在我的gradle构建中有大量的任务，因为对于每个特定的工作元素，gradle都需要一个这种类型的任务。例如：复制一个文件分级需要复制任务类型，压缩一个文件分级需要压缩任务类型，删除几个文件分级需要删除任务类型。Javadoc、Jar、Ear等也需要它们的任务类型。最终，build有大量的任务，要以特定的顺序运行所有这些任务，我必须使用 将这些规则应用于所有

  • XSLT将最近的条目按键分组

    我需要按键值保留输入xml分组中的最新条目。可以用xsl 2.0实现吗？输入

  • 高级部分

    只要你看完了前面的查询和Mutation基本上就已经满足你的日常使用了,但是我们不能止步于此是吧,还有更美好的世界等着我们! 分片 在 GraphQL 中,分片是一段能够复用的片段. 如果我们需要查询三个不同文章的信息,那么我们可能会做如下的查询: { first:posts(index:1){ title, category, layout }, second

相关问答

Drools：获取最近的3个事件最近5年得sql查询与分组依据？pyspark-分组并选择N个最高值Apache是否支持分布式事务高分不会保存

相关文章

分组最大值T-SQL-按最接近的日期选择并按ID分组获取最高/最小的记录 每组带有高级混合索引的Numpy子数组分配最近在分区上使用row_number（）的SQL

相关阅读

高途-数分-一面Spring Tx (分布式事务及解决方案)MySQL过滤分组MySQL分组查询TestNG分组测试

相关工具

FineCMS高级版《Go语言高级编程》word分词近乎（SpaceBuilder）盘古分词

相关文档

Stack Overflow 关于 Python 的部分高分问题JavaScript 高级的知识点React 高级教程Java 提高篇Go 语言高级编程
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.