我的书签
添加书签
移除书签Standard library
The standard library currently contains commonly used functions that are used in SQL. It’s not yet as broad as we’d like, and we’re very open to expanding it.
Currently s-strings are an escape-hatch for any function that isn’t in our standard library. If we find ourselves using them for something frequently, raise an issue and we’ll add it to the stdlib.
Note
Currently the stdlib implementation doesn’t support different DB implementations itself; those need to be built deeper into the compiler. We’ll resolve this at some point. Until then, we’ll only add functions here that are broadly supported by most DBs.
Here’s the source of the current PRQL std:
1. # Aggregate Functions
2. func min <scalar || column> column -> null
3. func max <scalar || column> column -> null
4. func sum <scalar || column> column -> null
5. func avg <scalar || column> column -> null
6. func stddev <scalar || column> column -> null
7. func average <scalar || column> column -> null
8. func count <scalar || column> non_null:s"*" -> null
9. # TODO: Possibly make this into `count distinct:true` (or like `distinct:` as an
10. # abbreviation of that?)
11. func count_distinct <scalar || column> column -> null
13. # Window functions
14. func lag<column> offset column -> null
15. func lead<column> offset column -> null
16. func first<column> offset column -> null
17. func last<column> offset column -> null
18. func rank<column> -> null
19. func rank_dense<column> -> null
20. func row_number<column> -> null
22. # Other functions
23. func round<scalar> n_digits column -> null
24. func as<scalar> `noresolve.type` column -> null
25. func in<bool> pattern value -> null
27. # Transform type definitions
28. func from<table> `default_db.source`<table> -> null
29. func select<table> columns<column> tbl<table> -> null
30. func filter<table> condition<bool> tbl<table> -> null
31. func derive<table> columns<column> tbl<table> -> null
32. func aggregate<table> a<column> tbl<table> -> null
33. func sort<table> by tbl<table> -> null
34. func take<table> expr tbl<table> -> null
35. func join<table> `default_db.with`<table> filter `noresolve.side`:inner tbl<table> -> null
36. func group<table> by pipeline tbl<table> -> null
37. func window<table> rows:0..0 range:0..0 expanding:false rolling:0 pipeline tbl<table> -> null
39. func append<table> `default_db.bottom`<table> top<table> -> null
40. func intersect<table> `default_db.bottom`<table> top<table> -> (
41. from t = _param.top
42. join b = _param.bottom (all (map _eq (zip t.* b.*)))
43. select t.*
44. )
45. func remove<table> `default_db.bottom`<table> top<table> -> (
46. from t = _param.top
47. join side:left b = _param.bottom (all (map _eq (zip t.* b.*)))
48. filter (all (map _is_null b.*))
49. select t.*
50. )
51. func loop<table> pipeline top<table> -> null
53. # List functions
54. func all<bool> list<list> -> null
55. func map<list> fn list<list> -> null
56. func zip<list> a<list> b<list> -> null
57. func _eq<list> a<list> -> null
58. func _is_null a -> _param.a == null
60. # Misc
61. func from_text<table> input<text> `noresolve.format`:csv -> null
63. # String functions
64. func lower <text> column -> null
65. func upper <text> column -> null
67. # type primitives
68. type int
69. type float
70. type bool
71. type text
72. type date
73. type time
74. type timestamp
75. type table
76. type column
77. type list
78. type scalar
80. # Source-reading functions, primarily for DuckDB
81. func read_parquet<table> source<text> -> s"SELECT * FROM read_parquet({source})"
82. func read_csv<table> source<text> -> s"SELECT * FROM read_csv_auto({source})"
And a couple of examples:
PRQL
1. from employees
2. derive [
3. gross_salary = (salary + payroll_tax | as int),
4. gross_salary_rounded = (gross_salary | round 0),
5. time = s"NOW()", # an s-string, given no `now` function exists in PRQL
6. ]
SQL
1. SELECT
2. *,
3. CAST(salary + payroll_tax AS int) AS gross_salary,
4. ROUND(CAST(salary + payroll_tax AS int), 0) AS gross_salary_rounded,
5. NOW() AS time
6. FROM
7. employees
