Lewe zewnętrzne połączenie działające jak wewnętrzne połączenie

streszczenie

Moim celem jest znalezienie każdego użytkownika, który kiedykolwiek został przydzielony do zadania, a następnie wygenerowanie statystyk w określonym zakresie dat i powiązanie statystyk z oryginalnym zestawem użytkowników. Gdy nie ma statystyk dla konkretnego użytkownika, chcę wiersz na wyjściu dla użytkownika, aleNULL wartości statystyk.

Mam złożone zapytanie SQL, które wygląda tak (rzeczywiste zapytanie na dole):

SELECT
  user_name, changeday, project_name
  sum(hour_delta) AS hours,
FROM ( … ) tasked_users
LEFT OUTER JOIN ( … ) a
ON tasked_users.id=a.assignee_id
WHERE
  (changeday IS NULL) OR (changeday >= … AND changeday <= …)
GROUP BY user_name, changeday, a.project_name
ORDER BY user_name, changeday, a.project_name;

Moim pragnieniem jest znalezienie dużego zestawu użytkowników i dopasowanie ich do danych za stół; gdy istnieją użytkownicy, którzy nie mają pasujących wpisówa Chcę zera lub0 godziny.

Niestety, to zapytanie zwraca tylko wiersze dla użytkowników obecnych w 'a'. Na przykład jeden konkretny zestaw dat zwraca:

{:user_name=>"Gavin", :hours=>0.0, :changeday=>2013-09-08, :project_name=>"Foo"}
{:user_name=>"Steve", :hours=>1.0, :changeday=>2013-09-08, :project_name=>"Bar"}

Podczas gdy różne zakresy dat powodują znalezienie różnych użytkowników. Zawartośćtasked_users pod-zapytanie ma 14 różnych par identyfikator / nazwa użytkownika. potrzebujęwszystko z nich być reprezentowanym w wyniku.

Przykładowe zapytanie

W przypadku, gdy ma to znaczenie, lub jeśli masz dodatkowe pomocne wskazówki dotyczące ulepszenia zapytania, oto pełne zapytanie.

SELECT
  user_name,
  sum(hour_delta) AS hours,
  changeday,
  project_name
FROM (
  SELECT DISTINCT
    users.id,
    users.name AS user_name
  FROM users
  INNER JOIN tasks AS tasks1
  ON users.id=tasks1.assignee_id
) tasked_users
LEFT OUTER JOIN
(
  SELECT
    (
      coalesce(cast(nullif(new_value,'') AS float),0) -
      coalesce(cast(nullif(old_value,'') AS float),0)
    ) AS hour_delta,
    task_id,
    tasks2.assignee_id AS assigned_log,
    fixin_id,
    projects.name AS project_name,
    date_trunc('day',task_log_entries.created_on) AS changeday
  FROM task_log_entries
  INNER JOIN tasks AS tasks2
  ON task_id=tasks2.id
  INNER JOIN fixins
  ON fixins.id=tasks2.fixin_id
  INNER JOIN projects
  ON projects.id=fixins.project_id
  WHERE field_id=18
) a
ON tasked_users.id=a.assigned_log
WHERE
  (changeday IS NULL)
  OR
  (changeday >= '2013-09-08' AND changeday <= '2013-09-08')
GROUP BY user_name, changeday, a.project_name
ORDER BY user_name, changeday, a.project_name;
Wyjaśnij dane wyjściowe

Oto rezultatEXPLAIN dla zapytania, na wypadek gdyby to pomogło (nie wiem, jak to przeczytać i uzyskać to, czego potrzebuję):

GroupAggregate  (cost=1116.40..1116.99 rows=13 width=144)"}
  ->  Sort  (cost=1116.40..1116.43 rows=13 width=144)"}
        Sort Key: users.name, (date_trunc('day'::text, task_log_entries.created_on)), projects.name"}
        ->  Hash Left Join  (cost=1024.32..1116.16 rows=13 width=144)"}
              Hash Cond: (users.id = tasks2.assignee_id)"}
              Filter: ((date_trunc('day'::text, task_log_entries.created_on) IS NULL) OR ((date_trunc('day'::text, task_log_entries.created_on) >= '2013-09-08 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, task_log_entries.created_on) <= '2013-09-08 00:00:00'::timestamp without time zone)))"}
              ->  HashAggregate  (cost=44.07..45.46 rows=139 width=12)"}
                    ->  Hash Join  (cost=5.13..40.09 rows=795 width=12)"}
                          Hash Cond: (tasks1.assignee_id = users.id)"}
                          ->  Seq Scan on tasks tasks1  (cost=0.00..24.01 rows=801 width=4)"}
                          ->  Hash  (cost=3.39..3.39 rows=139 width=12)"}
                                ->  Seq Scan on users  (cost=0.00..3.39 rows=139 width=12)"}
              ->  Hash  (cost=963.51..963.51 rows=1339 width=30)"}
                    ->  Hash Join  (cost=729.23..963.51 rows=1339 width=30)"}
                          Hash Cond: (fixins.project_id = projects.id)"}
                          ->  Hash Join  (cost=727.91..943.79 rows=1339 width=24)"}
                                Hash Cond: (task_log_entries.task_id = tasks2.id)"}
                                ->  Seq Scan on task_log_entries  (cost=0.00..197.46 rows=1339 width=20)"}
                                      Filter: (field_id = 18)"}
                                ->  Hash  (cost=717.90..717.90 rows=801 width=12)"}
                                      ->  Hash Join  (cost=676.87..717.90 rows=801 width=12)"}
                                            Hash Cond: (tasks2.fixin_id = fixins.id)"}
                                            ->  Seq Scan on tasks tasks2  (cost=0.00..24.01 rows=801 width=12)"}
                                            ->  Hash  (cost=589.72..589.72 rows=6972 width=8)"}
                                                  ->  Seq Scan on fixins  (cost=0.00..589.72 rows=6972 width=8)"}
                          ->  Hash  (cost=1.14..1.14 rows=14 width=14)"}
                                ->  Seq Scan on projects  (cost=0.00..1.14 rows=14 width=14)"}
Definicje tabel

Oto opis wszystkich zaangażowanych tabel. Nie przyciąłem ich, aby usunąć jakiekolwiek „nieistotne” kolumny, więc możesz być pewien, że istnieją jakieś niejednoznaczne konflikty nazw kolumn.

app=> \d task_log_entries
                                     Table "public.task_log_entries"
   Column   |            Type             |                           Modifiers
------------+-----------------------------+---------------------------------------------------------------
 id         | integer                     | not null default nextval('task_log_entries_id_seq'::regclass)
 task_id    | integer                     | not null
 user_id    | integer                     |
 field_id   | integer                     | not null
 created_on | timestamp without time zone | not null default now()
 new_value  | text                        |
 old_value  | text                        |
Indexes:
    "task_log_entries_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "task_log_entries_field_id_fkey" FOREIGN KEY (field_id) REFERENCES log_fields(id)
    "task_log_entries_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    "task_log_entries_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL


app=> \d tasks
                                        Table "public.tasks"
     Column     |            Type             |                      Modifiers
----------------+-----------------------------+-----------------------------------------------------
 id             | integer                     | not null default nextval('fixins_id_seq'::regclass)
 fixin_id       | integer                     | not null
 created_on     | timestamp without time zone | not null default now()
 updated_on     | timestamp without time zone | not null default now()
 name           | character varying(200)      | not null
 description    | text                        |
 blocked_by     | character varying(200)      |
 estimate       | double precision            |
 actual         | double precision            |
 remaining      | double precision            |
 relative_order | integer                     |
 status_id      | integer                     | not null
 assignee_id    | integer                     |
Indexes:
    "tasks_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "tasks_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
    "tasks_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    "tasks_status_id_fkey" FOREIGN KEY (status_id) REFERENCES task_statuses(id)
Referenced by:
    TABLE "task_comments" CONSTRAINT "task_comments_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    TABLE "task_log_entries" CONSTRAINT "task_log_entries_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    TABLE "users_tasks_notifications" CONSTRAINT "users_tasks_notifications_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE


app=> \d fixins
                                       Table "public.fixins"
     Column     |            Type             |                      Modifiers
----------------+-----------------------------+-----------------------------------------------------
 id             | integer                     | not null default nextval('fixins_id_seq'::regclass)
 project_id     | integer                     | not null
 created_on     | timestamp without time zone | not null default now()
 updated_on     | timestamp without time zone | not null default now()
 name           | character varying(200)      | not null
 description    | text                        | not null
 status_id      | integer                     | not null
 reporter_id    | integer                     |
 assignee_id    | integer                     |
 priority_id    | integer                     | not null
 severity_id    | integer                     | not null
 likelihood_id  | integer                     | not null
 maturity       | integer                     | not null default 0
 version        | character varying(100)      |
 iteration_id   | integer                     |
 relative_order | integer                     |
 kind           | character varying(16)       | not null default 'Bug'::character varying
 specs          | character varying(50)       |
 estimate       | double precision            |
 blocked_by     | character varying(200)      |
 plan_estimate  | double precision            |
 actual         | double precision            |
 remaining      | double precision            |
 promise_date   | date                        |
Indexes:
    "fixins_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fixins_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
    "fixins_iteration_id_fkey" FOREIGN KEY (iteration_id) REFERENCES iterations(id) ON DELETE SET NULL
    "fixins_likelihood_id_fkey" FOREIGN KEY (likelihood_id) REFERENCES likelihoods(id)
    "fixins_priority_id_fkey" FOREIGN KEY (priority_id) REFERENCES priorities(id)
    "fixins_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)
    "fixins_reporter_id_fkey" FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE SET NULL
    "fixins_severity_id_fkey" FOREIGN KEY (severity_id) REFERENCES severities(id)
    "fixins_status_id_fkey" FOREIGN KEY (status_id) REFERENCES statuses(id)
Referenced by:
    TABLE "bug_snapshots" CONSTRAINT "bug_snapshots_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE SET NULL
    TABLE "comments" CONSTRAINT "comments_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "customers_fixins" CONSTRAINT "customers_fixins_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id)
    TABLE "fixins_tags" CONSTRAINT "fixins_tags_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "log_entries" CONSTRAINT "log_entries_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "relationships" CONSTRAINT "relationships_fixin1_id_fkey" FOREIGN KEY (fixin1_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "relationships" CONSTRAINT "relationships_fixin2_id_fkey" FOREIGN KEY (fixin2_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "tasks" CONSTRAINT "tasks_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "users_notifications" CONSTRAINT "users_notifications_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id) ON DELETE CASCADE
    TABLE "votes" CONSTRAINT "votes_fixin_id_fkey" FOREIGN KEY (fixin_id) REFERENCES fixins(id)


app=> \d projects
                                     Table "public.projects"
     Column     |          Type           |                       Modifiers
----------------+-------------------------+-------------------------------------------------------
 id             | integer                 | not null default nextval('projects_id_seq'::regclass)
 name           | character varying(50)   | not null
 link_name      | character varying(50)   | not null
 pain_threshold | integer                 | not null
 wiki_server    | character varying(100)  |
 wiki_wiki      | character varying(100)  |
 wiki_pattern   | character varying(1000) |
 active         | boolean                 | not null default true
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
    "projects_link_name_key" UNIQUE, btree (link_name)
Referenced by:
    TABLE "fixins" CONSTRAINT "fixins_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)
    TABLE "iterations" CONSTRAINT "iterations_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    TABLE "project_preferences" CONSTRAINT "project_preferences_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)
    TABLE "projects_users_notifications" CONSTRAINT "projects_users_notifications_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)
    TABLE "releases" CONSTRAINT "releases_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE


app=> \d users
                                 Table "public.users"
  Column  |         Type          |                     Modifiers
----------+-----------------------+----------------------------------------------------
 id       | integer               | not null default nextval('users_id_seq'::regclass)
 name     | character varying(50) | not null
 email    | character varying(50) |
 active   | boolean               | not null default true
 passhash | character varying(40) |
 salt     | character varying(4)  |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "comments" CONSTRAINT "comments_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "fixins" CONSTRAINT "fixins_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "fixins" CONSTRAINT "fixins_reporter_id_fkey" FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "log_entries" CONSTRAINT "log_entries_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "project_preferences" CONSTRAINT "project_preferences_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    TABLE "projects_users_notifications" CONSTRAINT "projects_users_notifications_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    TABLE "task_comments" CONSTRAINT "task_comments_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "task_log_entries" CONSTRAINT "task_log_entries_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "tasks" CONSTRAINT "tasks_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
    TABLE "users_notifications" CONSTRAINT "users_notifications_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    TABLE "users_tasks_notifications" CONSTRAINT "users_tasks_notifications_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE


app=> \d log_fields
          Table "public.log_fields"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(200) | not null
Indexes:
    "log_fields_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "log_entries" CONSTRAINT "log_entries_field_id_fkey" FOREIGN KEY (field_id) REFERENCES log_fields(id)
    TABLE "task_log_entries" CONSTRAINT "task_log_entries_field_id_fkey" FOREIGN KEY (field_id) REFERENCES log_fields(id)

questionAnswers(2)

yourAnswerToTheQuestion