forked from taozhi8833998/node-sql-parser
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathredshift.spec.js
More file actions
63 lines (57 loc) · 3.73 KB
/
redshift.spec.js
File metadata and controls
63 lines (57 loc) · 3.73 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
const { expect } = require('chai');
const Parser = require('../src/parser').default
describe('redshift', () => {
const parser = new Parser();
const DEFAULT_OPT = { database: 'redshift' }
function getParsedSql(sql, opt = DEFAULT_OPT) {
const ast = parser.astify(sql, opt);
return parser.sqlify(ast, opt);
}
it('should support qualify condition', () => {
let sql = `SELECT sf_account_id, date_trunc('week', date_) as week_id, date_trunc('month', date_) as month_id, Last_VALUE(hermes_health_score) IGNORE NULLS OVER ( partition by sf_account_id, week_id ) as hermes_health_score,
Last_VALUE(hermes_health_score) IGNORE NULLS OVER ( partition by sf_account_id, month_id ) as hermes_health_score_monthly, row_number() OVER ( PARTITION BY sf_account_id, date_trunc('week', date_)
ORDER BY date_ desc ) AS o_key_week FROM dwh.dwh_health_score_hermes WHERE date_trunc('month', date_) >= '2023-01-01' Qualify o_key_week = 1`
expect(getParsedSql(sql)).to.be.equal(`SELECT sf_account_id, date_trunc('week', date_) AS "week_id", date_trunc('month', date_) AS "month_id", Last_VALUE(hermes_health_score) IGNORE NULLS OVER (PARTITION BY sf_account_id, week_id) AS "hermes_health_score", Last_VALUE(hermes_health_score) IGNORE NULLS OVER (PARTITION BY sf_account_id, month_id) AS "hermes_health_score_monthly", row_number() OVER (PARTITION BY sf_account_id, date_trunc('week', date_) ORDER BY date_ DESC) AS "o_key_week" FROM "dwh"."dwh_health_score_hermes" WHERE date_trunc('month', date_) >= '2023-01-01' QUALIFY o_key_week = 1`)
sql = `with pv as (
select
action_date,
visitor_id_v,
visit_country_name,
referer_channel_group,
email,
sgid,
mp."brand/non-brand" as is_brand
from
dwh_fact_pageviews pv
left join ppc_keywords_mapping mp using (campaign_keyword)
)
select is_brand as "b/nb" from pv where mp."brand/non-brand" = 'brand'`
expect(getParsedSql(sql)).to.be.equal(`WITH "pv" AS (SELECT action_date, visitor_id_v, visit_country_name, referer_channel_group, email, sgid, "mp"."brand/non-brand" AS "is_brand" FROM "dwh_fact_pageviews" AS "pv" LEFT JOIN "ppc_keywords_mapping" AS "mp" USING (campaign_keyword)) SELECT is_brand AS "b/nb" FROM "pv" WHERE "mp"."brand/non-brand" = 'brand'`)
})
it('should support unary operator', () => {
const sql = 'SELECT * FROM montara_raw.raw_listings WHERE price = -minimum_nights'
expect(getParsedSql(sql)).to.be.equal('SELECT * FROM "montara_raw"."raw_listings" WHERE price = -minimum_nights')
})
it('should support no space before line comment', () => {
const sql = 'select * from model_a a-- comment'
expect(getParsedSql(sql)).to.be.equal('SELECT * FROM "model_a" AS "a"')
})
it('should support position function', () => {
const sql = `SELECT pv.email,
pv.action_date_id,
SUM(CASE
WHEN event_category_name = 'Download'
AND POSITION('PNG' IN event_full_name) > 0
THEN 1
ELSE 0
END) AS download_png
FROM dwh.dwh_fact_pageviews pv
WHERE action_date_id >= '2022-01-01'
GROUP BY 1, 2`
expect(getParsedSql(sql)).to.be.equal(`SELECT "pv".email, "pv".action_date_id, SUM(CASE WHEN event_category_name = 'Download' AND POSITION('PNG' IN event_full_name) > 0 THEN 1 ELSE 0 END) AS "download_png" FROM "dwh"."dwh_fact_pageviews" AS "pv" WHERE action_date_id >= '2022-01-01' GROUP BY 1, 2`)
})
it('should support multiple cast', () => {
const sql = "SELECT COUNT(DISTINCT event_time::DATE::TEXT || '_' || event_type) FROM my_events"
expect(getParsedSql(sql)).to.be.equal(`SELECT COUNT(DISTINCT event_time::DATE::TEXT || '_' || event_type) FROM "my_events"`)
})
})