根据季度划分ID，并通过确定季度来计算1或0 [英] Divide Ids based on quarter and the count either 1 or 0 by determining the quarter

问题描述

我们有两列 Id 和 month Id 。

我期望的输出是基于季度粒度将年份从月份ID中分出。活动专栏应该从四分之一开始。如果id是活动的，活动应该是1 else 0。如果id在第一季度中有任何活动（例如：只有1），那么活动仍然是1.

像这样：

  id month_dt 
 -------------------- --------------- 
 1000000000 2012-03-01 00：00：00.0 
 1000000000 2015-09-01 00：00：00.0 
 1000000000 2016-10-01 00：00：00.0 
 1000000000 2015-11-01 00：00：00.0 
 1000000000 2014-01-01 00：00：00.0 
 1000000000 2013-04-01 00：00：00.0 
 1000000000 2014-12-01 00：00：00.0 
 1000000000 2015-02-01 00：00：00.0 
 1000000000 2014-06-01 00：00：00.0 
 1000000000 2013-01-01 00：00：00.0 
 1000000000 2014-05-01 00：00：00.0 
 1000000000 2016-05-01 00：00：00.0 
 1000000000 2013-07-01 00：00：00.0 
  

期望值：

  ID年份QTR活动（1或0）
 -------------------- ------------------------------ 
 1000000000 2012 1 1 
 1000000000 2012 2 0 
 1000000000 2012 3 0 
 1000000000 2012 4 0 
 1000000000 2013 1 1 
 1000000000 2013 2 1 
 1000000000 2013 3 1 
 1000000000 2013 4 0 
  

下面是我尝试过的一个，但它不会返回预期的结果。请帮我实现这一点

  SELECT 
 a.id，a.year，
 SUM（CASE WHEN季度间隔1和3然后1其他0结束）作为Q1，
总和（案例当季度在4和6之间，然后1和0结束时）作为Q2，
总和（案例当季度在7和9之间时1 ELSE 0 END）因为Q4 
 FROM 
（SELECT 
 id，
 TRIM（SUBSTRING（month_id，1，4））as year，
 TRIM（regexp_replace（SUBSTR（month_id，5，4）， - ，））as quarter 
 FROM 
 test.patientid）a 
 GROUP BY 
 a.id，a.year 
  

解决方案

我想你正在寻找这样的东西：

  select y.yyyy， （t.month_dt）> 0 then 1 else 0 end）作为activity_flag 
 from（从t选择不同年份（month_dt）为yyyy）y交叉连接
 （选择不同的季度（ （t.month_dt）= y.yyyy和quarter（t.month_dt）= q.q 
由y.yyyy，q.q组成的组合; 
  

假定一年中每个季度至少有一项活动（无论季度为何） 。否则，你只需要输入一个1，2，3和4的列表即可获得宿舍。

We have two columns Id and month Id.

The output what I'm looking for is to divide year from month Id based on quarter granularity. The activity column should be from quarter. If id is active activity should be 1 else 0 .If id comes in any of the 1st quarter (eg:only 1) the activity is still 1 .

Like this:

id           month_dt
-----------------------------------
1000000000   2012-03-01 00:00:00.0
1000000000   2015-09-01 00:00:00.0
1000000000   2016-10-01 00:00:00.0
1000000000   2015-11-01 00:00:00.0
1000000000   2014-01-01 00:00:00.0
1000000000   2013-04-01 00:00:00.0
1000000000   2014-12-01 00:00:00.0
1000000000   2015-02-01 00:00:00.0
1000000000   2014-06-01 00:00:00.0
1000000000   2013-01-01 00:00:00.0
1000000000   2014-05-01 00:00:00.0
1000000000   2016-05-01 00:00:00.0
1000000000   2013-07-01 00:00:00.0

What is expected:

ID           YEAR     QTR      ACTIVITY (1 or 0)
--------------------------------------------------
1000000000   2012      1          1
1000000000   2012      2          0
1000000000   2012      3          0
1000000000   2012      4          0
1000000000   2013      1          1
1000000000   2013      2          1
1000000000   2013      3          1
1000000000   2013      4          0

Below is the one I tried but it doesn't return the expected results. Please help me achieve this

SELECT
    a.id, a.year,
    SUM(CASE WHEN quarter BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter BETWEEN 4 AND 6 THEN 1 ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter BETWEEN 7 AND 9 THEN 1 ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter BETWEEN 10 AND 12 THEN 1 ELSE 0 END) AS Q4
FROM
    (SELECT
         id, 
         TRIM(SUBSTRING(month_id, 1, 4)) AS year,
         TRIM(regexp_replace(SUBSTR(month_id, 5, 4), "-", "")) as quarter    
     FROM
         test.patientid) a
GROUP BY 
    a.id, a.year

解决方案

I think you are looking for something like this:

select y.yyyy, q.q,
       (case when count(t.month_dt) > 0 then 1 else 0 end) as activity_flag
from (select distinct year(month_dt) as yyyy from t) y cross join
     (select distinct quarter(month_dt) as q from t) q left join
     t
     on year(t.month_dt) = y.yyyy and quarter(t.month_dt) = q.q
group by y.yyyy, q.q;

This assumes that there is at least one activity for each quarter in a year (regardless of the quarter). Otherwise, you just need to put in a list of 1, 2, 3, and 4 to get the quarters.

这篇关于根据季度划分ID，并通过确定季度来计算1或0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！