Sql 在没有订单的情况下，如何生成一个值为0的顾客天数行

with Datas as (
select distinct 
   CAST(o.created_at AS DATE) datas
from platform_bs."order" o
where date_format(o.created_at, '%Y') = '2023'
order by 1
),-- here I put it to list all the dates from 2023 until now
count_orders_client as(
SELECT
   datas,
   od.client_id,
   nullif(count(od.id),0) as count
from Datas as dt
left join platform_bs."order" od ON dt.datas = CAST(od.created_at AS DATE)
group by 1,2
order by 1,2
)-- here I do a left join to get the dates, client id and a count and where should I do it if there were no records on the day, it would return 0
select * from count_orders_client where client_id = 10552

-- sample data
with dataset(created_at, order_status_id, client_id) as (
    values (timestamp '2023-04-05 22:19:29.000',    3,  '11'),
        (timestamp '2023-04-06 23:47:36.000',   3,  '2'),
        (timestamp '2023-04-07 23:42:22.000',   3,  '11'),
        (timestamp '2023-04-06 19:54:33.000',   4,  '2'),
        (timestamp '2023-04-08 12:11:06.000',   3,  '2')
),
-- query parts
clients as ( -- find all unique clients
    select distinct client_id
    from dataset
),
client_dates as ( -- create cartesian product of clients and dates
    select *
    from clients,
    -- use here needed start and end for `sequence`:
    unnest(sequence(date '2023-04-04', date '2023-04-09', interval '1' day)) as t(dt) 

)

select cd.client_id, dt, count(order_status_id) count
from client_dates cd
left join dataset d
    on cd.dt = date(created_at) and cd.client_id = d.client_id
group by cd.client_id, cd.dt;

Sql相关问答推荐

如何返回字符串中包含相同值的数据？

在SQL中向每个子字节组添加字节行

用于平均多个数据并与一个数据点进行比较以判断偏移量的SQL查询

如何在联接条件不匹配时按日期获取上一条记录

如何根据计数和分组获取订单总数

DBeaver将过程中的属性列表转换为字符串

SQL：如何在表中同时使用GROUPING和CONDITION？

重用传递给 node 的参数-postgres upsert查询

动态组/转置

如何为该查询编写正确分区依据

通过对象分离实现原子性

存储过程太慢

将二维数组的第一个和第二个元素取消嵌套到两个一维数组中

递归 CTE 附加为行

在presto sql中解析带有区域的时间格式

BigQuery数组是否包含NULL值的判断方法

自动生成计算频率的列

SQL Select 最大并获取列名

SQL中所有先前日期的累计总和

如何刷新在视图之上创建的表