每月/每年计数不同，但在查询结果中显示所有日期

qpgpyjmq 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(359)

小提琴

CREATE TABLE sales (
    id int auto_increment primary key,
    orderID VARCHAR(255),
    sent_date DATE
);

INSERT INTO sales
(orderID, sent_date
)
VALUES 
("Order_01", "2019-03-15"),
("Order_01", "2019-03-16"),
("Order_02", "2020-06-16"),
("Order_03", "2020-07-27"),
("Order_03", "2020-08-05"),
("Order_03", "2020-08-10");

预期结果：

sent_date      COUNT(distinct orderID)
2019-03-15              1
2019-03-16              0
2020-06-16              1
2020-07-27              1
2020-08-05              1
2020-08-10              0

在上表中，我有同样的答案 orders 具有多个 sent_dates .
现在，我要数一数 orderIDs 每 month/year 每天。
因此，我正在寻找一个查询来检查每个 orderID 如果它已经存在于上一个 sent_date 如果是的话，电流的值 sent_date 应该是 0 .
我知道每月/每年唯一计数的最简单方法是以下查询：

SELECT
YEAR(sent_date),
MONTH(sent_date),
COUNT(distinct orderID)
FROM sales
GROUP BY 1,2;

但是，我需要每一个 sent_date 在列表中单独显示，如预期结果所示。
虽然我每天都需要查询数据，但我需要什么查询才能使计数唯一？

sql mysql window-functions Date count

来源：https://stackoverflow.com/questions/63173546/count-distinct-per-month-year-but-display-all-dates-in-the-query-result

2条答案

按热度按时间

8fq7wneg1#

我认为您需要一个每个orderid每个日期的第一个月出现次数的柱状图。
下面是一种使用mysql 8.0中提供的窗口函数的方法：

select sent_date, sum(rn = 1) cnt_distinct_orderid
from (
    select s.*, row_number() over(partition by year(sent_date), month(sent_date), orderid order by sent_date) rn
    from sales s
) t
group by sent_date
order by sent_date

在早期版本中，有一个选项使用自联接：

select d.sent_date, count(s.orderid) cnt_distinct_orderid
from (select distinct sent_date from sales) d
left join (
    select orderid, min(sent_date) min_sent_date
    from sales 
    group by orderid, date_format(sent_date, '%Y-%m-01')
) s on d.sent_date = s.min_sent_date
group by d.sent_date

小提琴上的恶魔：

sent_date  | cnt_distinct_orderid
:--------- | -------------------:
2019-03-15 |                    1
2019-03-16 |                    0
2020-06-16 |                    1
2020-07-27 |                    1
2020-08-05 |                    1
2020-08-10 |                    0

赞(0）回复(0）举报 2021-07-27

ercv8c1e2#

如果我理解正确的话，您希望标记每个订单在一个月内的第一次出现。可以使用窗口函数：

select s.*,
       ( row_number() over (partition by extract(year_month from sent_date), orderid order by sent_date) = 1 ) as flag
from s;

mysql 8+支持窗口函数。

赞(0）回复(0）举报 2021-07-27

我来回答

每月/每年计数不同，但在查询结果中显示所有日期

2条答案

相关问题

热门标签

最新问答