Ich habe diese Eingabetabelle

+--------+-----------+------------+----------+
| TaskId | OwnerName | WorkerName | Category |
+--------+-----------+------------+----------+
|      1 | Sara      | Sara       |        1 |
|      1 | Sara      | Maya       |        1 |
|      1 | Sara      | Sara       |        1 |
|      2 | Sara      | Sara       |        0 |
|      2 | Sara      | Sara       |        0 |
|      3 | Sam       | Sam        |        1 |
|      3 | Sam       | Sam        |        1 |
|      3 | Sam       | Sam        |        1 |
|      4 | Ella      | Ella       |        1 |
|      4 | Ella      | Ella       |        1 |
|      5 | Ella      | Ella       |        1 |
|      6 | Ella      | Ella       |        0 |
+--------+-----------+------------+----------+

Ich möchte berechnen, wie oft der Name des Eigentümers in der Kategoriespalte hoch (1) oder niedrig (0) steht.

Die Aufgaben sollten als eindeutig gezählt werden, auch wenn eine Aufgabe den Eigentümernamen hat! = Arbeitername ... die gesamte Aufgabe wird gelöscht.

Beispielsweise wird TaskId 1 gelöscht.

Erwartete Ausgabe:

+-----------+------------------+-----------------+-------+------+
| OwnerName | #UniqueHighTasks | #UniqueLowTasks | %High | %Low |
+-----------+------------------+-----------------+-------+------+
| Sara      |                0 |               1 | 0     | 100  |
| Sam       |                1 |               0 | 100   | 0    |
| Ella      |                2 |               1 | 66%   | 33%  |
+-----------+------------------+-----------------+-------+------+

Mein Versuch berücksichtigt nicht, dass die eindeutigen IDs und die Aufgabe 1 gelöscht werden. Wie kann das erreicht werden?

Versuch:

SELECT 
      [OwnerName], 


  (100 * COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0) /
  (COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0) + COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0))) AS %High,

  (100 * COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0) /
  (COALESCE(COUNT(CASE
    WHEN [Category] = 0 THEN 1
  END), 0) + COALESCE(COUNT(CASE
    WHEN [Category] = 1 THEN 1
  END), 0))) AS %Low,

  SUM(case [Category] when 1 then 1 else 0 end) AS '#UniqueHighTasks',
  SUM(case [Category] when 0 then 1 else 0 end) AS '#UniqueLowTasks'


   FROM [dbo].[mytable]
  Group by  [OwnerName]
2
sara 17 Apr. 2018 im 18:24

4 Antworten

Beste Antwort

Versuchen Sie diese Abfrage:

declare @t table (TaskId int, OwnerName varchar(10), WorkerName varchar(10), Category int)
insert into @t
values 
    (1, 'Sara','Sara', 1), (1, 'Sara','Maya', 1)
    , (1, 'Sara','Sara', 1), (2, 'Sara','Sara', 0)
    , (2, 'Sara','Sara', 0), (3, 'Sam','Sam', 1)
    , (3, 'Sam','Sam', 1), (3, 'Sam','Sam', 1)
    , (4, 'Ella','Ella', 1), (4, 'Ella','Ella', 1)
    , (5, 'Ella','Ella', 1), (6, 'Ella','Ella', 0)

select
    OwnerName, UniqueHighTasks, UniqueLowTasks
    , [%High] = UniqueHighTasks * 100.0 / (UniqueHighTasks + UniqueLowTasks)
    , [%Low] = UniqueLowTasks * 100.0 / (UniqueHighTasks + UniqueLowTasks)
from (
    select
        OwnerName, UniqueHighTasks = count(distinct case when Category = 1 then TaskId end)
        , UniqueLowTasks =  count(distinct case when Category = 0 then TaskId end)
    from
        @t
    where
        TaskId not in (select TaskId from @t where OwnerName <> WorkerName)
    group by OwnerName
) t
1
uzi 17 Apr. 2018 im 15:58

Ich denke, das wird es tun

declare @T table (TaskId int, OwnerName varchar(20), WorkerName varchar(20), Category int);
insert into @T values
  (1, 'Sara', 'Sara', 1 )
, (1, 'Sara', 'Maya', 1 )
, (1, 'Sara', 'Sara', 1 )
, (2, 'Sara', 'Sara', 0 )
, (2, 'Sara', 'Sara', 0 )
, (3, 'Sam',  'Sam',  1 )
, (3, 'Sam',  'Sam',  1 )
, (3, 'Sam',  'Sam',  1 )
, (4, 'Ella', 'Ella', 1 )
, (4, 'Ella', 'Ella', 1 )
, (5, 'Ella', 'Ella', 1 )
, (6, 'Ella', 'Ella', 0 );
with cte as 
( select distinct t.TaskId, t.OwnerName, t.Category 
  from @T t
  where TaskID not in (select TaskId from @T where OwnerName <> WorkerName)
)
select cte.OwnerName 
     , count(*) taskCount 
     , sum(category) as highCount
     , count(*) - sum(category) as lowCount 
     , 100.0*sum(category)/count(*) as highPct
     , 100.0*(count(*)-sum(category))/count(*) as lowPct
from cte 
group by cte.OwnerName

OwnerName            taskCount   highCount   lowCount    lowPct                                 highPct
-------------------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
Ella                 3           2           1           66.666666666666                         33.333333333333
Sam                  1           1           0           100.000000000000                        0.000000000000
Sara                 1           0           1           0.000000000000                          100.000000000000
1
paparazzo 17 Apr. 2018 im 16:26

Nehmen Sie Ihre aktuelle Abfrage und anstatt die Tabelle direkt abzufragen, fragen Sie einen CTE oder eine abgeleitete Tabelle ab, die einfach ein SELECT DISTINCT aus der Tabelle ausführt.

0
Tab Alleman 17 Apr. 2018 im 15:42

Sie könnten das versuchen

WITH nonDup AS (
  SELECT DISTINCT [TaskId], [OwnerName], [WorkerName], [Category] 
  FROM mytable t1
  WHERE NOT EXISTS(SELECT 1 FROM mytable t2 WHERE t1.TaskID = t2.TaskID and t2.OwnerName <> t2.WorkerName)
),
calc AS (
  SELECT 
      OwnerName,
      SUM(Category) AS UniqueHighTasks,
      SUM(CASE WHEN Category = 0 THEN 1 ELSE 0 END) AS UniqueLowTasks
  FROM nonDup
  GROUP BY OwnerName
)
SELECT OwnerName,UniqueHighTasks,UniqueLowTasks,
    CASE WHEN UniqueHighTasks+ UniqueLowTasks<> 0 THEN UniqueHighTasks*1.0/(UniqueHighTasks+ UniqueLowTasks) END AS [%High],
    CASE WHEN UniqueHighTasks+ UniqueLowTasks<> 0 THEN UniqueLowTasks*1.0/(UniqueHighTasks+ UniqueLowTasks) END AS [%Low]
FROM calc
0
EricZ 17 Apr. 2018 im 15:46