luoganttcc的博客

#coding:utf8conn = MySQLdb.connect(host = “localhost”, user = “root”, passwd = “123456”, db = “myDB”)cursor = conn.cursor()sql = “insert into myTable (created_day,name,count) values(%s,%s,%s) ON DUPLICATE KEY UPDATE count=count+values(count)”args=[("20

import pandas as pdimport refrom impala.dbapi import connectfrom impala.util import as_pandasfrom impala.dbapi import connectimport json,sysfrom datetime import datetimeconn=connect(host='127.0.0.1', port=21050, database='ods',timeout = 30)cursor=

WITH Numbers AS (SELECT 90 as A, 2 as B UNION ALL SELECT 50, 8 UNION ALL SELECT 60, 6 UNION ALL SELECT 50, 10)SELECT A, B, CASE A WHEN 90 THEN 'red' WHEN 50 THEN 'blue' ELSE 'green' END AS resultFROM NumbersABresult9

WITH Produce AS (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category UNION ALL SELECT 'orange', 2, 'fruit' UNION ALL SELECT 'cabbage', 9, 'vegetable' UNION ALL SELECT 'apple', 8, 'fruit' UNION ALL SELECT 'leek', 2, 'vegetable' UNION

select SUBSTR('我爱中国我爱毛主席', 0, 4) as cutstr行cutstr1我爱中国

WITH buy_fruit AS (SELECT 'Sophia Liu' as name, 9 as price ,'apple' as fruit UNION ALL SELECT 'Lisa Stelzner', 5,'pear' UNION ALL SELECT 'Nikki Leith', 3 ,'banana' UNION ALL SELECT 'Lauren Matthews', 11,'grape' UNION ALL SELECT 'Lisa St

WITH finishers AS (SELECT 'Sophia Liu' as name, 'F30-34' as division ,'apple' as fruit UNION ALL SELECT 'Lisa Stelzner', 'F35-39','pear' UNION ALL SELECT 'Nikki Leith', 'F30-34' ,'banana' UNION ALL SELECT 'Lauren Matthews', 'F35-39','grape'.

import osos.environ["GOOGLE_APPLICATION_CREDENTIALS"] = config.credentials_path

with A as (SELECT * FROM `heidao-market.mafia1_dmd.exchange_info` WHERE DATE(timestamp) > "2010-09-21" and item like '%21056810%')select charge_id ,MAX(timestamp )as ma from A group by charge_id SELECT * FROM `heidao-market.mafia1_dmd.item_info` WHE

sql md5 最简单例子select MD5('0j46o1112665989') as mdd有时候需要将多个字符串拼接select MD5(CONCAT('0j46o','1112665989')) 行mdd51gU89+XO93zUEg4w39N7U0A==将MD5 的字节码转化为 16进制SELECT TO_HEX(MD5(CONCAT('0j46o', CAST(1112665989 AS STRING)))) 行s16181

句法SUBSTR(string, start, length)SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;ExtractStringTut

select CAST(FLOOR(101/5) as int64) as group_id行group_id120

SELECT * from UNNEST(ARRAY<STRUCT<lev INT64, power FLOAT64>>[(1, 1),(2, 1.4), (3, 1.9), (4, 2.5), (5, 3.2), (6, 4), (7, 4.9), (8, 5.9), (9, 7), (10, 8.2), (11, 9.5)])

查询嵌套数组 SELECT "800M" AS race, [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits), STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits), STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits), STRUCT("Bosse"

展平数组SELECT *FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS elementWITH OFFSET AS offsetORDER BY offset;elementoffsetfoo0bar1baz2qux3corge4garply5waldo6fred7如需展平一整列 ARRAY，同时

访问数组元素WITH sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers)SELECT some_numbers, some_numbers[OFFSET(1)] AS offset_1, some_numbers[OR

SELECT [1, 2, 3] as numbers;SELECT ["apple", "pear", "orange"] as fruit;SELECT [true, false, true] as booleans;SELECT [a, b, c]FROM (SELECT 5 AS a, 37 AS b, 406 AS c);SELECT [a, b, c]FROM (SELECT CAST(5 AS INT64) AS a,

with lev_info as(SELECT * from UNNEST(ARRAY<STRUCT<lev INT64, power FLOAT64>>[(1, 1),(2, 1.4), (3, 1.9), (4, 2.5), (5, 3.2), (6, 4), (7, 4.9), (8, 5.9), (9, 7), (10, 8.2), (11, 9.5)])),army_info as (select army_id, freq, CASE WHEN army_id

mysql -u root -puse mysql;update user set host='%' where user ='root';FLUSH PRIVILEGES; #刷新权限GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;参考链接

链接

with f as (select * from UNNEST([1, 3,8,9,13,23, 5, 4, 23,55,77,6]) as fruit ) SELECT *, STRING_AGG(cast(fruit as string), " , ") OVER (ORDER BY fruit ROWS BETWEEN 0 FOLLOWING AND 3 FOLLOWING) as click_historyFROM f where fruit>4 ;STRING_AGG 的.

SELECT fruit, STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 0 FOLLOWING AND 3 FOLLOWING) AS string_aggFROM UNNEST(["apple", "pear", "banana", "pear",'aa','cc','dfef']) AS fruit;SELECT fruit , STRING_AGG(cast(fruit a

SELECT STRING_AGG(fruit) AS string_aggFROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;string_aggapple,pear,banana,pearSELECT STRING_AGG(fruit, " & ") AS string_aggFROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit

使用线性插值计算 value_expression 的指定百分位值。如果不存在 RESPECT NULLS，此函数会忽略 NULL 值。如果存在 RESPECT NULLS：两个 NULL 值之间的插值返回 NULL。一个 NULL 值与一个非 NULL 值之间的插值返回非 NULL 值。以下示例计算一个值列中的某些百分位值，同时忽略 null。SELECT PERCENTILE_CONT(x, 0) OVER() AS min, PERCENTILE_CONT(x, 0.01) OV

返回上一行中的 value_expression 值。更改 offset 值会改变所返回的上一行；默认值是 1，表示窗口框架中的上一行。WITH finishers AS (SELECT 'Sophia Liu' as name, TIMESTAMP '2016-10-18 2:51:45' as finish_time, 'F30-34' as division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11'

返回后续行的 值。更改 offset 值会改变所返回的后续行；默认值是 1，表示窗口框架中的下一行WITH finishers AS (SELECT 'Sophia Liu' as name, TIMESTAMP '2016-10-18 2:51:45' as finish_time, 'F30-34' as division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39' UNION

返回当前窗口框架中第 N 行的 值WITH finishers AS (SELECT 'Sophia Liu' as name, TIMESTAMP '2016-10-18 2:51:45' as finish_time, 'F30-34' as division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39' UNION ALL SELECT 'Nikki Leith', TIMES

| | |namefinish_timedivisionfastest_time delta_in_secondsCarly Forte 03:08:58 F25-29 03:08:58 0Sophia Liu 02:51:45 F30-34 02:51:45 0Nikki Leith 02:59:01 F30-34 02:51:45 436Jen Edwards 03:06:36 F30-34 02:51:45 891Meghan Lederer 03:0

本sql 可以在mysql 8.0 和谷歌的bigquery 中运行WITH Roster AS (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL SELECT 'Buchanan', 52 UNION ALL SELECT 'Coolidge', 52 UNION ALL SELECT 'Davis', 51 UNION ALL SELECT 'Eisenhower', 77)select * from Roster wh

biqguerySELECT TIME "15:30:00" as original_time, TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;original_timelater15:30:0015:40:00SELECT "2020-08-08 15:30:00" as original_time, date_ADD( "2020-08-08 15:30:00", INTERVAL 10 d

SELECT IFNULL(a, b); 如果a是null 返回b,否则返回aSELECT IFNULL(null, "RUNOOB");IFNULL(null, “RUNOOB”)RUNOOBSELECT IFNULL("Hello", "RUNOOB");IFNULL(null, “RUNOOB”)Hello

SELECT TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp, TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp, TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;google bigquery 里面是前面的值减

WITH TeamMascot AS (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL SELECT 51, 'Knights' UNION ALL SELECT 52, 'Lakers' UNION ALL SELECT 53, 'Mustangs')SELECT * ,(select 3) as add_column_1, (select 6) as add_column_2, FROM TeamMascot

WITH Words AS ( SELECT word FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word)SELECT CODE_POINTS_TO_STRING([code_point]) AS letter, COUNT(*) AS letter_countFROM Words, UNNEST(TO_CODE_POINTS(word)) AS code_pointGROUP BY 1ORDER BY

WITH example AS (SELECT "абвгд" AS characters, b"абвгд" AS bytes)SELECT characters, BYTE_LENGTH(characters) AS string_example, bytes, BYTE_LENGTH(bytes) AS bytes_exampleFROM example;+------------+----------------+-------+---------------+| .

mysql> SELECT CURRENT_DATE() as the_date;+------------+| the_date |+------------+| 2020-07-21 |+------------+mysql> SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;+---------+| the_day |+---------+| 25 |+---------+1 row .

将数组转换为字符串利用 ARRAY_TO_STRING() 函数，您可以将 ARRAY 转换为单个 STRING 值，或者将 ARRAY 转换为单个 BYTES 值，其中，生成的值是有序连接的数组元素。第二个参数是分隔符，相应函数将在输入之间插入该分隔符以生成输出；第二个参数必须与第一个参数的元素具有相同类型。示例：WITH greetings AS (SELECT ["Hello", "World"] AS greeting)SELECT ARRAY_TO_STRING(greeting,

WITH fruits AS (SELECT "apple" AS fruit UNION ALL SELECT "pear" AS fruit UNION ALL SELECT "banana" AS fruit)SELECT ARRAY_AGG(fruit) AS fruit_basketFROM fruits;+-----------------------+| fruit_basket |+-----------------------+| [appl

扫描数组如需检查数组是否包含特定值，请结合使用 IN 运算符和 UNNEST。如需检查数组是否包含与某条件匹配的值，请结合使用 EXISTS 函数和 UNNEST。扫描特定值如需扫描数组中的特定值，请将 IN 运算符与 UNNEST 结合使用。示例以下示例在数组中包含数字 2 时返回 true。SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;+----------------+| contains_value |+----

过滤数组以下示例在 ARRAY() 运算符的子查询中使用 WHERE 子句，以过滤返回的行。WITH sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers UNION ALL SELECT [5, 10] AS some_numbers)SELECT ARRAY(SELECT x * 2 FROM UNNES