英文:
pandas read_sql. How to query with where clause of date field
问题
I have a field month-year which is in datetime64[ns] format.
我有一个字段月份-年份，它的格式是datetime64[ns]。
How do i use this field in where clause to get rolling 12 months data(past 12 months data).
如何在where子句中使用这个字段来获取滚动的12个月数据（过去12个月的数据）。
Below query does not work, but I would like something that filters data for 12 months.
下面的查询不起作用，但我想要一个可以筛选12个月数据的方法。
select * from ABCD.DEFG_TABLE where monthyear > '2019-01-01';
从ABCD.DEFG_TABLE中选择所有数据，其中monthyear > '2019-01-01'。
FYI - It is an oracle database. If i can avoid hard coding the value 2019-01-01 that would be great!!
FYI - 这是一个Oracle数据库。如果我能避免硬编码值2019-01-01，那将会很好！！
英文:
I have a field month-year which is in datetime64[ns] format.
How do i use this field in where clause to get rolling 12 months data(past 12 months data).
Below query does not work, but I would like something that filters data for 12 months.
select * from ABCD.DEFG_TABLE where monthyear > '2019-01-01'
FYI - It is an oracle database. If i can avoid hard coding the value 2019-01-01 that would be great!!
答案1
得分: 1
以下是已翻译的内容：
你需要使用 datetime 并设置日期格式如下。
只需获取你的相对日期，如果你遵循 datetime 格式为 YYYYMMDD，可以使用 date time 的 strftime 与正则表达式字符串为 ("%Y%m%d")。
import datetime
import pandas
from dateutil.relativedelta import relativedelta
query = "SELECT * FROM ng_scott.Emp"
between_first = datetime.date.today()
between_second = between_first - relativedelta(years=1)
# 获取数据集
dataset = pd.read_sql(query , con=engine)
# 解析数据集
filtered_dataset = dataset[(dataset['DOJ'] > between_first ) & (dataset['DOJ'] > between_second )]
print(filtered_dataset)
英文:
You need to use the datetime and set the date format as below.
Just get your relative date and if you follow datetime format as YYYYMMDD, use strftime from date time with regex string as ("%Y%m%d")
import datetime
import pandas
from dateutil.relativedelta import relativedelta
query = "SELECT * FROM ng_scott.Emp"
between_first = datetime.date.today()
between_second = between_first - relativedelta(years=1)
# GET THE DATASET
dataset = pd.read_sql(query , con=engine)
# PARSE THE DATASET
filtered_dataset = dataset[(dataset['DOJ'] > between_first ) & (dataset['DOJ'] > between_second )]
print(filtered_dataset)
答案2
得分: 0
你可以使用纯SQL来完成这个任务。
以下表达式动态计算了1年前的月初日期：
add_months(trunc(sysdate, 'month'), -12)
这句话的意思是：取当前月份的第一天的日期，并从中减去12个月。
你可以将其用作筛选条件：
select * from ABCD.DEFG_TABLE where monthyear > add_months(trunc(sysdate, 'month'), -12)
注意：这假设monthyear的数据类型是date。
英文:
You can do this with pure SQL.
The following expression dynamically computes the beginning of the months 1 year ago:
add_months(trunc(sysdate, 'month'), -12)
This phrases as: take the date at the first day of the current month, and withdraw 12 months from it.
You can just use it as a filter condition:
select * from ABCD.DEFG_TABLE where monthyear > add_months(trunc(sysdate, 'month'), -12)
NB: this assumes that monthyear is of datatype date.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论