英文:

How to flag all records for a date when at least two records for that date have same duration OR at least one record has 0 durtation

问题

日期	代码	时长	标记
4/2/23	AB	510	是
4/2/23	SH	510	是
4/2/23	B2	15	是

同时包括0

日期	代码	时长	标记
4/2/23	AB	0	是
4/2/23	SH	510	是
4/2/23	B2	15	是

期望结果:

日期	代码	时长	标记
4/2/23	AB	510	是
4/2/23	SH	510	是
4/2/23	B2	15	是

或者

日期	代码	时长	标记
4/2/23	AB	0	是
4/2/23	SH	510	是
4/2/23	B2	15	是

英文:

I need to flag records for a date when there are the same duration or at least one of the records have 0 duration. CODE SH is the duration being matched to or if another records has a 0 duration. Example Records being analyzed:

Date	Code	Duration
4/2/23	AB	510
4/2/23	SH	510
4/2/23	B2	15

also with 0

Date	Code	Duration
4/2/23	AB	0
4/2/23	SH	510
4/2/23	B2	15

Expect this:

Date	Code	Duration	FLAG
4/2/23	AB	510	Y
4/2/23	SH	510	Y
4/2/23	B2	15	Y

OR

Date	Code	Duration	FLAG
4/2/23	AB	0	Y
4/2/23	SH	510	Y
4/2/23	B2	15	Y

I was going to do an analytic function that counted records using code and date as partition.

count(1) over (partition by date,code,duration)

but then realized how do I handle 0's as well ? Thanks for any help.

答案1

得分: 2

以下是翻译好的部分：

You may use count and min window functions as the following:

    with t as 
    (
      select tb.*,
          count(*)  over  (partition by date_, code, Duration) cnt,
          min(Duration) over (partition by date_, code) min_dur
      from tbl tb
    ) 
    select t.date_, t.code, t.duration,
      case 
        when min_dur = 0 or -- there is a 0 duration
             max(cnt) over (partition by date_, code) > 1 -- when there are the same durations in multiple rows. 
        then 'Y' else 'N'
      end as flg
    from t

demo

<details>
<summary>英文:</summary>

You may use count and min window functions as the following:

    with t as 
    (
      select tb.*,
          count(*)  over  (partition by date_, code, Duration) cnt,
          min(Duration) over (partition by date_, code) min_dur
      from tbl tb
    ) 
    select t.date_, t.code, t.duration,
      case 
        when min_dur = 0 or -- there is a 0 duration
             max(cnt) over (partition by date_, code) &gt; 1 -- when there are the same durations in multiple rows. 
        then &#39;Y&#39; else &#39;N&#39;
      end as flg
    from t

[demo][1]


  [1]: https://dbfiddle.uk/gPFKJ8Q0

</details>



# 答案2
**得分**: 1

我有些累，可能有一种更简单的方法我现在没能看到，但你可以对比上海和非上海的日期/持续时间，然后将持续时间为零的日期相加。

选择日期、代码、持续时间
来自我的表
其中日期在
(
选择日期从
(
选择日期、持续时间
来自我的表
其中代码 = 'SH'
交集
选择日期、持续时间
来自我的表
其中代码 <> 'SH'
联合全部
选择日期、持续时间
来自我的表
其中持续时间 = 0
)
);

或

选择日期、代码、持续时间
来自我的表 t
其中存在
(
选择日期、持续时间
来自我的表 t2
其中t2.日期 = t.日期 和 t2.代码 = 'SH'
交集
选择日期、持续时间
来自我的表 t3
其中t3.日期 = t.日期 和 t3.代码 <> 'SH'
)
或日期在
(
选择日期
来自我的表
其中持续时间 = 0
);


<details>
<summary>英文:</summary>

I am a bit tired, so there might be a much simpler method that I fail to see right now, but you can just intersect date/duration for SH and non-SH and then add the dates with a zero duration.

    select date, code, duration
    from mytable
    where date in
    (
      select date from
      (
        select date, duration
        from mytable
        where code = &#39;SH&#39;
        intersect
        select date, duration
        from mytable
        where code &lt;&gt; &#39;SH&#39;
        union all
        select date, duration
        from mytable
        where duration = 0
      )
    );

or

    select date, code, duration
    from mytable t
    where exists
    (
      select date, duration
      from mytable t2
      where t2.date = t.date and t2.code = &#39;SH&#39;
      intersect
      select date, duration
      from mytable t3
      where t3.date = t.date and t3.code &lt;&gt; &#39;SH&#39;
    )
    or date in
    (
      select date
      from mytable
      where duration = 0
    );

</details>



# 答案3
**得分**: 1

ahmed的建议帮助我得到我所需的东西。我不得不稍微调整它，但它有效。实际上，我还需要一个额外的分区字段，用于员工ID。以下是我想出来的内容：

这个公共表达式查找不是SH的最大持续时间和最小持续时间。

,t as 
(
  select tb.*,
      max(case when code <> 'SH' then duration end)  over  (partition by nom_date, emp_id) mx,
      min(Duration) over (partition by nom_date,emp_id) min_dur
  from scd tb
) 

这些标记了我不需要的记录：

select 
to_char(nom_date,'MM-YYYY') Mnth,
nom_date,
EMP_ID,
from
(
select t.nom_date,t.emp_id, t.code, t.duration,
  case 
    when min_dur = 0 or -- there is a 0 duration
         max(case when code = 'SH' THEN duration end) over (partition by nom_date,emp_id) = max(mx) over (partition by nom_date,emp_id) -- when there are the same durations in multiple rows. 
    then 'Y' else 'N'
  end as flg
from t
)
where FLG = 'N' and CODE = 'SH'

<details>
<summary>英文:</summary>

ahmed&#39;s tip help get me what I needed.  I had to tweak it a bit but it worked.  I actually needed one more partitioned field as well for emp id.  Here&#39;s what I came up with:

This cte finds the max duration which is not SH and min duration.

    ,t as 
    (
      select tb.*,
          max(case when code &lt;&gt; &#39;SH&#39; then duration end)  over  (partition by nom_date, emp_id) mx,
          min(Duration) over (partition by nom_date,emp_id) min_dur
      from scd tb
    ) 

This flags records I do not need:

    select 
    to_char(nom_date,&#39;MM-YYYY&#39;)Mnth,
    nom_date,
    EMP_ID,
    from
    (
    select t.nom_date,t.emp_id, t.code, t.duration,
      case 
        when min_dur = 0 or -- there is a 0 duration
             max(case when code = &#39;SH&#39; THEN duration end) over (partition by nom_date,emp_id) = max(mx) over (partition by nom_date,emp_id) -- when there are the same durations in multiple rows. 
        then &#39;Y&#39; else &#39;N&#39;
      end as flg
    from t
    )
    where FLG = &#39;N&#39; and CODE = &#39;SH&#39;



</details>