DA @sqlhub
Showing posts older than #558 · Back to latest
import duckdb
from repeat_helper import repeat_executor
con = duckdb.connect(database=':memory:')
con.execute("""CREATE TABLE parking_violations AS SELECT "Summons Number", "Vehicle Make", "Issue Date" FROM read_csv_auto('/Users/chengzhizhao/projects/pandas_alternatives/Parking_Violations_Issued_-_Fiscal_Year_2017.csv', delim=',', SAMPLE_SIZE=-1);""")
con.execute("""SELECT COUNT(1) FROM parking_violations""")
print(con.fetchall())
# ## Фильтрация по марке автомобиля (для BMW)
@repeat_executor(times=5)
def test_filter():
con.execute("""
SELECT * FROM parking_violations WHERE "Vehicle Make" = 'BMW'
""")
return con.fetchall()
# # ## Группировка по марке автомобиля и подсчет
@repeat_executor(times=5)
def test_groupby():
con.execute("""
SELECT COUNT("Summons Number") FROM parking_violations GROUP BY "Vehicle Make"
""")
return con.fetchall()
# # # ## SELF join
@repeat_executor(times=5)
def test_self_join():
con.execute("""
SELECT a."Summons Number"
FROM parking_violations a
INNER JOIN parking_violations b on a."Summons Number" = b."Summons Number"
""")
return con.fetchall()
# ## оконная функция
@repeat_executor(times=5)
def test_window_function():
con.execute("""
SELECT *, ROW_NUMBER() OVER (PARTITION BY "Vehicle Make" ORDER BY "Issue Date")
FROM parking_violations
""")
return con.fetchall()
test_filter()
# Время медианы - 0.410 с
test_groupby()
# # Время медианы - 0.122 с
test_self_join()
# # Время медианы - 3.364 с
test_window_function()
# # Время медианы - 6.466 с
CREATE TABLE Colleges (
college_id INT PRIMARY KEY,
college_code VARCHAR(20),
college_country VARCHAR(20) DEFAULT 'Japan'
);-- Вставляем значение 'Japan' в столбец college_country
INSERT INTO Colleges (college_id, college_code)
VALUES (1, 'ARP76');
-- Вставляем значение 'UAE' в столбец college_country
INSERT INTO Colleges (college_id, college_code, college_country)
VALUES (2, 'JWS89', 'UAE');ALTER TABLE Colleges
ADD CONSTRAINT country_default
DEFAULT 'Japan' FOR college_country;
PostgreSQL
ALTER TABLE Colleges
ALTER COLUMN college_code SET DEFAULT 'Japan';
MySQL
ALTER TABLE Colleges
ALTER college_country SET DEFAULT 'Japan';
Oracle
ALTER TABLE Colleges
MODIFY college_country DEFAULT 'Japan';ALTER TABLE Colleges
ALTER COLUMN college_country DROP DEFAULT;
MySQL
ALTER TABLE Colleges
ALTER college_country DROP DEFAULT;
SELECT TOP 2 *
FROM Customers;SELECT TOP 2 first_name, last_name
FROM Customers;
SELECT first_name, age
FROM Customers
LIMIT 2;SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;SELECT *
FROM Customers
FETCH FIRST 2 ROWS ONLY;
select
salesid
,dateid
,sellerid
,buyerid
,qty
,first_value(dateid) over (partition by buyerid order by dateid) as first_purchase_dt
,lag(dateid) over (partition by buyerid order by dateid) as previous_purchase_dt
,sum(qty) over (partition by buyerid order by dateid rows between unbounded preceding and current row) as moving_qty
,row_number() over (partition by buyerid order by dateid) as order_number
from winsales
;SELECT *
FROM Customers
WHERE last_name LIKE 'R%';
with decoded as (
select
"transaction_id"
,"is_successful"
,"ts"
,decode("is_successful", 'true', 0, 'false', 1, 2) as "order_is_successful"
from transactions
),
ordered as (
select
"transaction_id"
,"is_successful"
,"ts"
,row_number() over(partition by "transaction_id" order by "order_is_successful" asc, "ts" desc) as rn
from decoded
)
select
"transaction_id"
,"is_successful"
,"ts"
from ordered
where rn = 1
;UPDATE orders
SET order_status = 'Completed'
WHERE order_id = 12345;UPDATE orders
SET order_status = 'Completed',
order_total = 100.00
WHERE order_id = 12345;
Здесь мы обновляем значения столбцов order_status и order_total таблицы orders для заказа с order_id равным 12345.