mysql - 获取过去 20 周三的数据:aws redshift
问题描述
我必须为 AWS-redshift 编写此查询,以获取最近 20 个星期三的数据,帮助!
SELECT
count(user_leads.id) AS lead_count, DATE(user_leads.created)
FROM
user_leads
join courses on user_leads.course_id = courses.id
left join users on user_leads.user_id = users.id
where
user_leads.created >= '2020-01-31'
AND user_leads.created < '2020-03-03'
AND courses.course_type !=4
AND users.email not like "%edureka%"
AND users.first_name not like "%test%"
AND weekday(user_leads) = 2
GROUP BY DATE(user_leads.created) DESC;
解决方案
使用str.replace()
:
一些文件.json:
[
"Date",
"17/04/2019",
"Skill",
"Travis",
"Repository",
"27,699 repository results"
][
"Date",
"17/04/2019",
"Skill",
"Kotlin",
"Repository",
"55,752 repository results"
]
因此:
with open('someFile.json', 'r') as fp:
content = fp.readlines()
content = [l.strip() for l in content if l.strip()]
for line in content:
if '][' in line:
print(line.replace('][','],['))
else:
print(line)
输出:
[
"Date",
"17/04/2019",
"Skill",
"Travis",
"Repository",
"27,699 repository results"
],[
"Date",
"17/04/2019",
"Skill",
"Kotlin",
"Repository",
"55,752 repository results"
]
编辑:
一个看起来很 json 的文件应该是:
一些文件.json:
[
{
"date": "Date",
"dt": "17/04/2019",
"skill": "Skill",
"travel": "Travis",
"repo": "Repository",
"dat": "27,699 repository results"
}
][
{
"date": "Date",
"dt": "17/04/2019",
"skill": "Skill",
"travel": "Kotlin",
"repo": "Repository",
"dat": "2327,699 repository results"
}
]
因此:
import json
with open('someFile.json', 'r') as file:
content = file.read()
clean = content.replace('][', ',') # cleanup here
json_data = json.loads(clean)
print(json_data)
输出:
[
{'date': 'Date', 'dt': '17/04/2019', 'skill': 'Skill', 'travel': 'Travis', 'repo': 'Repository', 'dat': '27,699 repository results'},
{'date': 'Date', 'dt': '17/04/2019', 'skill': 'Skill', 'travel': 'Kotlin', 'repo': 'Repository', 'dat': '2327,699 repository results'}
]
推荐阅读
- java - 调色板高于 255
- linear-programming - 我怎么知道是否还有其他解决方案?
- node.js - 带参数的节点获取 GET
- .net - F# 应用程序找不到我的任何导入
- dapr - 如何在 Dapr Pub/Sub 组件中以声明方式订阅多个主题?
- javascript - React UserContext 对象给出未定义的值?(无法映射)
- javascript - 我应该如何使用 react-quill 将 HTML 内容存储到数据库
- c++ - 将每个枚举成员关联到一个类并从该类获取通用信息
- r - 从 R Dataframe 中提取隐藏信息
- python - 向所有四个子图添加 y-ticks,但仅在两个子图中保留 x-ticks