英文:
Cannot get the group by query to take date into account
问题
你的查询接近正确，但你需要添加一个条件来筛选出在第一张表中日期之后的记录。以下是修改后的查询：
SELECT t1.uri,
t1.date,
t1.name,
COALESCE(t2.cou, 0) AS Count
FROM table1 t1
LEFT JOIN (
SELECT name, COUNT(name) AS cou
FROM table2 t2
WHERE t2.date <= t1.date
GROUP BY name
) t2 ON t1.name = t2.name
ORDER BY t1.uri;
这将为你提供按要求筛选的结果。
英文:
Table 1
| URI | Date | Name |
|---|---|---|
| 1 | 2020-03-05 | Fred |
| 2 | 2020-03-04 | Bob |
| 3 | 2020-03-03 | Fred |
| 4 | 2020-03-02 | Dave |
| 5 | 2020-03-01 | Dave |
| 6 | 2020-02-28 | Fred |
| 7 | 2020-02-27 | Bob |
| 8 | 2020-02-26 | Bob |
| 9 | 2020-02-25 | Fred |
| 10 | 2020-02-24 | Fred |
Table 2
| URI | Date | Name |
|---|---|---|
| 1 | 2020-03-05 | Fred |
| 2 | 2020-03-04 | Bob |
| 4 | 2020-03-02 | Dave |
| 5 | 2020-03-01 | Dave |
| 6 | 2020-02-28 | Fred |
| 8 | 2020-02-26 | Bob |
| 9 | 2020-02-25 | Fred |
| 10 | 2020-02-24 | Fred |
I am trying to count the number of times the name is counted in the second table, when the date is the below the date on the line of the first table. So the output should be:
| URI | Count | Name |
|---|---|---|
| 1 | 3 | Fred |
| 2 | 1 | Bob |
| 3 | 3 | Fred |
| 4 | 1 | Dave |
| 5 | 0 | Dave |
| 6 | 2 | Fred |
| 7 | 1 | Bob |
| 8 | 0 | Bob |
| 9 | 1 | Fred |
| 10 | 0 | Fred |
I have managed to get to code to group and count the times each name appears in the second table, but I can't get it to count only those where the date is below the line date in the first table. I was hoping there was a relatively simple solution for this but cannot seem to get anything to work:
SELECT t1.uri,
t1.date,
t1.name,
t2.cou
FROM table1 t1
INNER JOIN (select name,
count(name) AS cou
FROM table2 t2
GROUP BY name) t2
ON t1.name = t2.name
ORDER BY t1.uri
答案1
得分: 1
以下是已翻译的代码部分：
类似这样的代码应该可以工作：
with all_data as (
SELECT t1.URI, t1.TX_DATE, t1.FIRST_NAME
,t2.FIRST_NAME as count_name
FROM TABLE1 t1
LEFT OUTER JOIN TABLE2 t2 on t1.first_name = t2.first_name and t1.tx_date > t2.tx_date
)
SELECT URI, TX_DATE, FIRST_NAME, count(count_name)
from all_data
group by URI, TX_DATE, FIRST_NAME
order by URI, TX_DATE, FIRST_NAME
;
请注意，已进行必要的代码翻译，不包含其他内容。
英文:
Something like this should work:
with all_data as (
SELECT t1.URI, t1.TX_DATE, t1.FIRST_NAME
,t2.FIRST_NAME as count_name
FROM TABLE1 t1
LEFT OUTER JOIN TABLE2 t2 on t1.first_name = t2.first_name and t1.tx_date > t2.tx_date
)
SELECT URI, TX_DATE, FIRST_NAME, count(count_name)
from all_data
group by URI, TX_DATE, FIRST_NAME
order by URI, TX_DATE, FIRST_NAME
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论