Unión externa izquierda actuando como unión interna
Resumen
Mi objetivo es encontrar a cada usuario que alguna vez ha sido asignado a una tarea, y luego generar algunas estadísticas en un rango de fechas en particular, y asociar las estadísticas con el conjunto original de usuarios. Cuando no existen estadísticas para un usuario en particular, quiero una fila en la salida para el usuario peroNULL Valores para las estadísticas.
Tengo una consulta SQL compleja que se parece a esto (consulta real en la parte inferior):
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;
Mi deseo es encontrar un gran conjunto de usuarios y relacionarlos con los datos dela mesa; cuando existen usuarios que no tienen ninguna entrada coincidente ena Quiero nulos o0 horas
Desafortunadamente, esta consulta solo devuelve filas para los usuarios presentes en 'a'. Por ejemplo, un conjunto particular de fechas devuelve:
{: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"}
Mientras que diferentes rangos de fechas dan como resultado diferentes usuarios que se encuentran Los contenidos de latasked_users la subconsulta tiene 14 pares de ID / nombre de usuario distintos. Necesitotodos De ellos estar representados en el resultado.
En caso de que haga una diferencia, o en caso de que tenga más consejos útiles para mejorar la consulta, aquí tiene la consulta completa.
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;
Aquí está el resultado deEXPLAIN para la consulta, en caso de que ayude (no sé cómo leer esto y obtener lo que necesito):
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)"}
Aquí está la descripción de todas las tablas involucradas. No los he recortado para eliminar ninguna columna "irrelevante", por lo que puede estar seguro de ver si hay conflictos de nombres de columna ambiguos.
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)