blocks|key|4227592|text|因为您已经有了一个有效的查询，所以我将重点放在子选择上。这里可以使用的条件是CASE，特别是“搜索案例”。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4227593|case
when+extract(day+from+current_date)+<+15+then
++++extract(month+from+current_date+-+interval+'2+months')
else
++++extract(month+from+current_date+-+interval+'1+month')
end+case|code-block|syntax|javascript|4227594|例如，这可以用作where子句的一部分。|4227595|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS^0|12|4|12|4|0|0|0|8|5|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

Since you already have a working query, I concentrate on the subselect. The condition you can use here is <a href="https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS" rel="nofollow"><code>CASE</code></a>, especially "Searched CASE"

<pre><code>case
when extract(day from current_date) &lt; 15 then
 extract(month from current_date - interval '2 months')
else
 extract(month from current_date - interval '1 month')
end case
</code></pre>

This may be used as part of a <code>where</code> clause, for example.

blocks|key|2541586|text|首先，将查询简化为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2541587|select+to_char(transaction_date,+'YYYY-MM')+as+month,+brand,
+++++++count(distinct+members)+as+distinct_count+
from+source.table
group+by+members,+to_char(transaction_date,+'YYYY-MM'),+brand;|code-block|syntax|javascript|2541588|那么，你就会有麻烦了。问题是，一排(比如从8月20日起)需要分成两组。一个简单的group+by无法处理这个问题。那么，让我们使用union+all。我认为结果是这样的：|offset|length|style|CODE|2541589|select+date_trunc('month',+transaction_date)+as+month,+brand,
+++++++count(distinct+members)+as+distinct_count+
from+source.table
where+(date_trunc('month',+transaction_date)+<+date_trunc('month'+current_date)+-+interval+'1+month')+or
++++++(day(current_date)+>+15+and+date_trunc('month',+transaction_date)+=+date_trunc('month'+current_date)+-+interval+'1+month')
group+by+date_trunc('month',+transaction_date),+brand
union+all
select+date_trunc('month'+current_date)+-+interval+'1+month'+as+month,+brand,
+++++++count(distinct+members)+as+distinct_count+
from+source.table
where+(day(current_date)+<+15+and+date_trunc('month',+transaction_date)+=+date_trunc('month'+current_date)+-+interval+'1+month')
group+by+brand;|2541590|entityMap^0|0|0|14|8|1T|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

First, simplify the query to:

<pre><code>select to_char(transaction_date, 'YYYY-MM') as month, brand,
 count(distinct members) as distinct_count 
from source.table
group by members, to_char(transaction_date, 'YYYY-MM'), brand;
</code></pre>

Then, you are going to have a problem. The problem is that one row (say from Aug 20th) needs to go into two groups. A simple <code>group by</code> won't handle this. So, let's use <code>union all</code>. I think the result is something like this:

<pre><code>select date_trunc('month', transaction_date) as month, brand,
 count(distinct members) as distinct_count 
from source.table
where (date_trunc('month', transaction_date) &lt; date_trunc('month' current_date) - interval '1 month') or
 (day(current_date) &gt; 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by date_trunc('month', transaction_date), brand
union all
select date_trunc('month' current_date) - interval '1 month' as month, brand,
 count(distinct members) as distinct_count 
from source.table
where (day(current_date) &lt; 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by brand;
</code></pre>

blocks|key|4227689|text|下面是一些sudo代码，用于获取间隔的开始日期和结束日期。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4227690|开始日期：|4227691|date+DATE_TRUNC('month',+CURRENT_DATE+-+integer+15)+-+interval+'1+month'|offset|length|style|CODE|4227692|这将返回当前一个月后，只有在第15天，从那里你可以减去一个完整的月，以得到你的起点。|4227693|结束日期:要计算此值，请获取开始日期，加上一个月，减去一天。|4227694|entityMap^0|0|0|0|20|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@$F|S|G|T|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

Here is some sudo code to get the begin date and the end date for your interval.

Begin date: 

<code>date DATE_TRUNC('month', CURRENT_DATE - integer 15) - interval '1 month'</code>

This will return the current month only after the 15th day, from there you can subtract a full month to get your starting point.

End Date:
To calculate this, grab the begin date, plus a month, minus a day.

blocks|key|4227756|text|如果源表由transaction_date分区，则此语法(而不是用表达式掩蔽transaction_date+)启用分区分隔。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4227757|select++++++to_char(transaction_date,+'YYYY-MM')++++as+month
+++++++++++,count+(distinct+members)++++++++++++++++as+distinct_count
+++++++++++,brand+++++++++++++++++++++++++++++++++++as+brand

FROM++++++++source.table

where+++++++transaction_date++++between+date_trunc('month',+current_date)+-+case+when+extract+(day+from+current_date)+>=+15+then+1+else+2+end+*+interval+'1'+month
++++++++++++++++++++++++++++++++and+++++date_trunc('month',+current_date)+-+case+when+extract+(day+from+current_date)+>=+15+then+0+else+1+end+*+interval+'1'+month+-+interval+'1'+day

group+by++++to_char(transaction_date,+'YYYY-MM')
+++++++++++,brand
;|code-block|syntax|javascript|4227758|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If the source table is partitioned by transaction_date, this syntax (not masking transaction_date with expression) enables partitions eliminatation.

<pre><code>select to_char(transaction_date, 'YYYY-MM') as month
 ,count (distinct members) as distinct_count
 ,brand as brand

FROM source.table

where transaction_date between date_trunc('month', current_date) - case when extract (day from current_date) &gt;= 15 then 1 else 2 end * interval '1' month
 and date_trunc('month', current_date) - case when extract (day from current_date) &gt;= 15 then 0 else 1 end * interval '1' month - interval '1' day

group by to_char(transaction_date, 'YYYY-MM')
 ,brand
;
</code></pre>

The below query returns a distinct count of 'members' for a given month and brand (see image below).

<pre><code>select to_char(transaction_date, 'YYYY-MM') as month, brand,
 count(distinct UNIQUE_MEM_ID) as distinct_count
from source.table
group by to_char(transaction_date, 'YYYY-MM'), brand;
</code></pre>

The data is collected with a 15 day lag after the month closes (meaning September 2016 MONTHLY data won't be 100% until October 15). I am only concerned with monthly data.

The query I would like to build: Until the 15th of this month (October), last month's data (September) should reflect August's data. The current partial month (October) should default to the prior month and thus also to the above logic. 

After the 15th of this month, last month's data (September) is now 100% and thus September should reflect September (and October will reflect September until November 15th, and so on). 

The current partial month will always = the prior month. The complexity of the query is how to calc prior month.

This query will be ran on a rolling basis so needs to be dynamic.

To be clear, I am trying to build a query where distinct_count for the prior month (until end of current month + 15 days) should reflect (current month - 2) value (for each respective brand). After 15 days of the close of the month, prior month = (current month - 1).

Partial current month defaults to prior month's data. The 15 day value should be variable/modifiable.

<a href="https://i.stack.imgur.com/ZZ7b3.png" rel="nofollow"><img src="https://i.stack.imgur.com/ZZ7b3.png" alt="enter image description here"></a>

Select data with a rolling date criteria

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

下面的查询返回给定月份和品牌的不同的“成员”计数(见下图)。select to_char(transaction_date, 'YYYY-MM') as month, brand,   count(distinct UNIQUE_MEM_ID) as distinct_countfrom source.tablegro...

问使用滚动日期条件选择数据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用滚动日期条件选择数据EN