Левое внешнее соединение действует как внутреннее соединение
Резюме
Моя цель - найти каждого пользователя, которому когда-либо была назначена задача, а затем сгенерировать некоторую статистику за определенный диапазон дат и связать статистику с исходным набором пользователей. Если для конкретного пользователя не существует статистики, я хочу получить строку в выводе для пользователя, ноNULL значения для статистики.
У меня сложный запрос SQL, который выглядит следующим образом (фактический запрос внизу):
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;
Я хочу найти большой набор пользователей и сопоставить их с данными изa Таблица; когда существуют пользователи, у которых нет соответствующих записей вa Я хочу нули или0 ч.
К сожалению, этот запрос возвращает только строки для пользователей, присутствующих в «а». Например, возвращается один конкретный набор дат:
{: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"}
В то время как разные диапазоны дат приводят к обнаружению разных пользователей. Содержаниеtasked_users подзапрос имеет 14 различных пар идентификаторов / имен пользователей. я нуждаюсьвсе из них должны быть представлены в результате.
Если это имеет значение, или если у вас есть дополнительные полезные советы по улучшению запроса, вот полный запрос.
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;
Вот результатEXPLAIN для запроса, в случае, если это помогает (я не знаю, как это прочитать и получить то, что мне нужно):
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)"}
Вот описание всех задействованных таблиц. Я не обрезал их, чтобы удалить любые «нерелевантные» столбцы, чтобы вы могли убедиться, что есть какие-либо неоднозначные конфликты имен столбцов.
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)