重金求解 SQL 编程问题， 不是SAS 里的proc sql

重金求解 SQL 编程问题，是微软的sql 服务器，不是SAS里的proc sql。 没找到sql 版，就到这里问了。 模型数据 2014.xls (38.5 KB)

2015-8-28 11:55:19 上传

　这是模型数据2014, 我写的ＳＱＬ　ｃｏｄｅ也在这里，但是有错误，还在练习中，但是时间不等人。　


悬赏 400 金币， 不够可以到留学区再奖励，金币不是问题。 为了避免金币通货膨胀所以不能一次悬赏太多。 最全面且能用的回答，经试验后会得到最高奖励。　


具体看这个附件里的模拟数据。由于给出数据只是例子，胡乱写的，所以可能无法给出精确的结果。但是可以从数据中看出问题的所在。实际数据近百万，太大了，放不开。

我已经通过 Excel 实现了以下结果，但是太麻烦了，花了一个多小时。不知道能否有大牛可以帮我在ＳＱＬ里实现以下想要的结果，要最后的结果基本不需要再在Ｅｘｃｅｌ里清理才行。

如果觉得半年的那个更换ｐｌａｎ条件有些苛刻，那就先假设每个人在一年里都不会更换 plan，但是每年年末可以更换也可以取消。（这个悬赏 400 金币）　

如果可以的话，请把半年的那个更换条件也考虑进去，这样可以全面解决问题。（如果能解决的话，悬赏金币数量可以增加）。



给出条件和问题:

以下提到的平均分计算方法是把每个 plan 的半年或一年里（视情况定）的所有 member 的对应 points 列出来并加和，而且要把ｍｅｍｂｅｒ数量统计出来并加和。在Ｅｘｃｅｌ里用两个sum if相除就可以实现，但是ｓｑｌ里就不同了。　通过观察数据可以看出ｍｅｍｂｅｒＩＤ在一年里是不变的，所以一年里根据 claim 和 medicine 数据统计出的 member ID 数量实际是 number of total claims。　一个ｍｅｍｂｅｒ可以只看医生不拿药，也可以只拿药不看医生，虽然这种人很少，但是有时候会有missing data就是因为这种理论可能。


1. Start date 指的是每年12个月，2014或2015。 但是最后的结果要总结成每年或每半年视情况而定。

2. 这个医疗赔款claimpaid 和药费medicinepaid 不是每个月都会有的，所以要忽略没有的年非

3. 这个会员号是固定的，所以每年12个月不变。

4. 这个plan 有很多种，为了理解简单，拿出4个做比较。Med A， Med B 算个人买的类型，Com A, Com B算公司买的类型。一旦有member 选了Med　ｐｌａｎ，半年里只可以在7月初换一次，也可以不换，但是不能取消ｐｌａｎ，每年年末再重新选是否签新的ｐｌａｎ。 4 种 plan 可以互换。


我想知道的是２０１４年这一年，有多少人是上半年加入 plan med A 的，加入后有多少人至少留到２０１４年底，又有多少人在下半年换了plan 。　留到２０１４年底的又有多少人在２０１５年初不再买任何ｐｌａｎ或者更换plan了，有多少人继续留在 plan med A 直到２０１５年６月底。　今年７月初又有更换plan，最后剩下多少人。　

还有就是在２０１４年下半年，１５年上下半年加入med plan A的也要单独列出来，这样根据plan name　把每个plan　的在每个时间段新加入和更换ｐｌａｎ或者退出的人树和对应的分数列出来。　这样最终我就能有每个plan 在各个时间段里老用户和新用户的人数和分数，可以做对比。　

再算平均分的时候我要的是至少半年的member人数和分数。是用每半年的总分数除以总人数算出半年的平均分，这个只对中途更换plan 的人来说的。　如果一年里没有更换过plan 的只需要把这一年的平均分算出来即可。２０１４和２０１５　都没有换过ｐｌａｎ的老用户也要只算一年的平均分，然后做对比，因为每年ＺＦ的政策不一样。　

２０１４年下半年加入ｐｌａｎ　Ａ的新用户，可以把他们在这下半年的平均分算出来。

此外我还想要每个更换ｐｌａｎ的用户在哪个时间段退出或者更换新ｐｌａｎ的，　然后他们的分数在每个阶段的分数是多少。　这个ｔａｂｌｅ　要按ｍｅｍｂｅｒ　来列，所以和之前提到过的所有ｔａｂｌｅ　都不一样。　要列成４列２０１４年和２０１５年上下半年的分数，这个不需要算平均分。　至于从每有更换过ｐｌａｎ　的，也可以这样统计出来他门的分数。　这个容易些，可以再加　１００　金币。　

谢谢。　

求可以用的正解，不是大体的思路

我只考虑了一年里每个member不会更换plan, 但实际情况是某些人会在每年7月处更换plan。 所以请大牛在解决的时候把问题考虑全面，谢谢。　

可能我对这个领域不熟悉，看了半天也没看出来你想要什么，
我建议你把结果表的字段列出来，以及简短的说明。
目前我的理解是，按半年1-6月，7-12月划分，按plan划分，计算member的次数，以及points的平均分？

我先解决个小问题，空值替换的问题，你可以用COALESCE(medicinepaid,0)将medicinepaid中的空值替换成0

wangluan 发表于 2015-8-28 14:04
可能我对这个领域不熟悉，看了半天也没看出来你想要什么，
我建议你把结果表的字段列出来，以及简短的说明 ...

这个半年1-6， 7-12月划分是要看情况的，可以理解成这样，但不是每个member都会在每年中途更换plan， 有的人只在年末更换，所以只需要看这些人整年的平均分。 但是我也要每个plan下的所有memberID和他们每月的points, 不论他是更换过的还是没换的，把每个memberID在一年里 看医生med 或者拿药 medicine 的次数（一个月里两者有一个发生即可，都出现算一次）还有平均分算出来。  

如果2014和2015都出现某个member,那么就要把这个member每年的数据单独列出来，以做对比。 由于时间紧，我昨天只是大体描述了一下情况，没有总结清楚。 周末会在更新帖子。 希望得到更全面的答复。 谢谢。

wangluan 发表于 2015-8-28 14:08
我先解决个小问题，空值替换的问题，你可以用COALESCE(medicinepaid,0)将medicinepaid中的空值替换成0

这个我用了medicinepaid is not NULL　或者　claimpaid  is not NULL应该可以达到一样的效果。　请全面回答问题，我到周六上午更新帖子，把问题总结一下。　

1、按substr(Startdate,1,4),plan,memberID分组
2、计算points的平均分
3、计算subste(Plan,1,3)=="Med"或medicinePaid>0的次数

1.    SELECT (SUBSTR(t1.Startdate,1,4)) AS Year
   
2.           ,t1.Plan
   
3.           ,t1.MemberID
   
4.           ,(AVG(COALESCE(t1.Points,0))) AS Points_AVG
   
5.           ,(SUM(CASE WHEN SUBSTR(t1.Plan,1,3) = "Med" or COALESCE(t1.MedicinePaid,0)> 0
   
6.                       THEN 1
   
7.                       ELSE 0
   
8.                END)
   
9.            ) AS MED_TIMES
   
10.          
    
11.       FROM TMP_TABLE t1
    
12.       GROUP BY SUBSTR(t1.Startdate,1,4),
    
13.                t1.Plan,
    
14.                t1.MemberID;

复制代码

结果集
Year        Plan        MemberID        Points_AVG        MED_TIMES
2014        COM A        114257        0.6        4
2014        Com A        675732        0        0
2014        Com B        234234        0        0
2014        Med A        114257        1        6
2014        Med A        118521        0.575        12
2014        Med A        155191        0        12
2014        Med A        342357        0        12
2014        Med A        675732        0        6
2014        Med A        753452        0        12
2014        Med A        883234        0        12
2014        Med B        185212        0.5        12
2015        COM B        114821        0.416666667        3
2015        Com A        155191        0        0
2015        Com A        234234        0        0
2015        Com A        456134        0        0
2015        Com B        183522        0        0
2015        Com B        253351        0        0
2015        Med A        155191        0        6
2015        Med A        183522        0        6
2015        Med A        456134        0        12
2015        Med A        523411        0        12
2015        Med B        114821        0.45        6
2015        Med B        155191        0        6
2015        Med B        253351        0        6
2015        Med B        342357        0        12
2015        Med B        456134        0        6

wangluan 发表于 2015-8-29 01:45
1、按substr(Startdate,1,4),plan,memberID分组
2、计算points的平均分
3、计算subste(Plan,1,3)=="Med"或 ...

这个解决的不全面，只是把date 分开但是更换plan的人呢？我想知道的是２０１４年这一年，有多少人是上半年加入比如ｐｌａｎ　ｍｅｄ　Ａ的，加入后有多少人至少留到２０１４年底，又有多少人在下半年换了ｐｌａｎ。　留到２０１４年底的又有多少人在２０１５年初不再买任何ｐｌａｎ或者更换ｐｌａｎ了，有多少人继续留在ｍｅｄ　Ａ直到２０１５年６月底。　今年７月初又有更换ｐｌａｎ，最后剩下多少人。　

还有就是在２０１４年下半年，１５年上下半年加入ｍｅｄ　ｐｌａｎ　Ａ的也要单独列出来，这样根据ｐｌａｎ　ｎａｍｅ　把每个ｐｌａｎ　的在每个时间段新加入和更换ｐｌａｎ或者退出的人树和对应的分数列出来。　这样最终我就能有每个ｐｌａｎ在各个时间段里老用户和新用户的人数和分数，可以做对比。　

再算平均分的时候我要的是至少半年的ｍｅｍｂｅｒ人数和分数。是用每半年的总分数除以总人数算出半年的平均分，这个只对中途更换ｐｌａｎ的人来说的。　如果一年里没有更换过ｐｌａｎ的只需要把这一年的平均分算出来即可。２０１４和２０１５　都没有换过ｐｌａｎ的老用户也要只算一年的平均分，然后做对比，因为每年ＺＦ的政策不一样。　

２０１４年下半年加入ｐｌａｎ　Ａ的新用户，可以把他们在这下半年的平均分算出来。

此外我还想要每个更换ｐｌａｎ的用户在哪个时间段退出或者更换新ｐｌａｎ的，　然后他们的分数在每个阶段的分数是多少。　这个ｔａｂｌｅ　要按ｍｅｍｂｅｒ　来列，所以和之前提到过的所有ｔａｂｌｅ　都不一样。　要列成４列２０１４年和２０１５年上下半年的分数，这个不需要算平均分。　至于从每有更换过ｐｌａｎ　的，也可以这样统计出来他门的分数。　这个容易些，可以再加　１００　金币。　

谢谢。　

wangluan 发表于 2015-8-29 01:45
1、按substr(Startdate,1,4),plan,memberID分组
2、计算points的平均分
3、计算subste(Plan,1,3)=="Med"或 ...

更新主题内容了，希望解决更全面些。