sql
sql
luoganttcc
微信:luogantt
展开
-
python executemany
#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原创 2021-09-30 18:56:35 · 140 阅读 · 0 评论 -
python读取 hive数据
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=原创 2021-08-03 14:53:01 · 779 阅读 · 0 评论 -
sql case 语句
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原创 2020-11-12 21:40:38 · 194 阅读 · 0 评论 -
sql 移动平均
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原创 2020-11-11 20:02:49 · 1291 阅读 · 0 评论 -
sql substr切割字符串
select SUBSTR('我爱中国我爱毛主席', 0, 4) as cutstr行cutstr1我爱中国原创 2020-11-05 19:58:23 · 500 阅读 · 0 评论 -
sql case
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原创 2020-11-05 15:26:57 · 163 阅读 · 0 评论 -
sql 聚合用户最近购买的水果,并且转成字符串
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'.原创 2020-11-04 17:01:03 · 272 阅读 · 0 评论 -
bigqury 认证
import osos.environ["GOOGLE_APPLICATION_CREDENTIALS"] = config.credentials_path原创 2020-09-28 15:07:39 · 121 阅读 · 0 评论 -
英雄推荐
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原创 2020-09-21 21:43:30 · 153 阅读 · 0 评论 -
sql md5
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原创 2020-09-19 16:10:30 · 2830 阅读 · 0 评论 -
sql SUBSTR
句法SUBSTR(string, start, length)SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;ExtractStringTut原创 2020-09-19 14:08:08 · 226 阅读 · 0 评论 -
sql floor 取整函数
select CAST(FLOOR(101/5) as int64) as group_id行group_id120原创 2020-09-19 12:02:21 · 433 阅读 · 0 评论 -
sql生成两列数据
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)])原创 2020-09-05 17:19:17 · 425 阅读 · 0 评论 -
sql array 数组基本用法(四)
查询嵌套数组 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"原创 2020-09-05 17:18:19 · 3004 阅读 · 0 评论 -
sql array 数组基本用法(三)
展平数组SELECT *FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS elementWITH OFFSET AS offsetORDER BY offset;elementoffsetfoo0bar1baz2qux3corge4garply5waldo6fred7如需展平一整列 ARRAY,同时原创 2020-09-05 16:18:14 · 6777 阅读 · 0 评论 -
sql 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原创 2020-09-05 15:27:27 · 13826 阅读 · 0 评论 -
sql array 数组基本用法(一)
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,原创 2020-09-05 15:03:39 · 20393 阅读 · 0 评论 -
sql item_map
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原创 2020-09-05 14:24:51 · 365 阅读 · 0 评论 -
mysql8 允许外网访问
mysql -u root -puse mysql;update user set host='%' where user ='root';FLUSH PRIVILEGES; #刷新权限GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;参考链接原创 2020-08-20 14:29:03 · 972 阅读 · 0 评论 -
Ubuntu 安装 MySQL 8.0+
链接原创 2020-08-19 17:42:31 · 154 阅读 · 0 评论 -
sql STRING_AGG 作用优先级
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 的.原创 2020-08-19 12:25:51 · 518 阅读 · 0 评论 -
sql 聚合函数 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原创 2020-08-18 17:00:07 · 1057 阅读 · 0 评论 -
sql 聚合函数 STRING_AGG
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原创 2020-08-18 15:46:20 · 2595 阅读 · 0 评论 -
sql PERCENTILE_CONT 计算一组数的线性差值
使用线性插值计算 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原创 2020-08-17 21:28:02 · 1164 阅读 · 0 评论 -
sql 导航函数 lag
返回上一行中的 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'原创 2020-08-17 21:17:17 · 713 阅读 · 0 评论 -
sql 导航函数 lead
返回后续行的 值。更改 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原创 2020-08-17 20:52:49 · 487 阅读 · 0 评论 -
sql导航函数 NTH_VALUE
返回当前窗口框架中第 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原创 2020-08-17 20:36:21 · 1278 阅读 · 0 评论 -
sql 中的导航函数 FIRST_VALUE, LAST_VALUE
| | |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原创 2020-08-17 17:10:06 · 361 阅读 · 0 评论 -
sql between
本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原创 2020-08-14 16:54:07 · 113 阅读 · 0 评论 -
sql date_sub 和 date_add
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原创 2020-08-14 16:44:32 · 316 阅读 · 0 评论 -
sql IFNULL
SELECT IFNULL(a, b); 如果a是null 返回b,否则返回aSELECT IFNULL(null, "RUNOOB");IFNULL(null, “RUNOOB”)RUNOOBSELECT IFNULL("Hello", "RUNOOB");IFNULL(null, “RUNOOB”)Hello原创 2020-08-14 14:45:06 · 221 阅读 · 0 评论 -
sql TIMESTAMP_DIFF计算时间差
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 里面是前面的值减原创 2020-08-13 21:08:55 · 2076 阅读 · 0 评论 -
sql 增加一列
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原创 2020-07-28 20:34:56 · 949 阅读 · 0 评论 -
sql 字符串函数二()统计单词字母出现频率
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原创 2020-07-27 19:16:30 · 534 阅读 · 0 评论 -
sql 字符串函数(一)
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;+------------+----------------+-------+---------------+| .原创 2020-07-27 14:58:43 · 157 阅读 · 0 评论 -
sql 日期函数
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 .原创 2020-07-21 20:46:23 · 155 阅读 · 0 评论 -
sql数组转换为字符串
将数组转换为字符串利用 ARRAY_TO_STRING() 函数,您可以将 ARRAY 转换为单个 STRING 值,或者将 ARRAY 转换为单个 BYTES 值,其中,生成的值是有序连接的数组元素。第二个参数是分隔符,相应函数将在输入之间插入该分隔符以生成输出;第二个参数必须与第一个参数的元素具有相同类型。示例:WITH greetings AS (SELECT ["Hello", "World"] AS greeting)SELECT ARRAY_TO_STRING(greeting,原创 2020-07-22 17:10:09 · 11687 阅读 · 0 评论 -
数组和聚合
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原创 2020-07-22 17:08:33 · 304 阅读 · 0 评论 -
sql 扫描数组
扫描数组如需检查数组是否包含特定值,请结合使用 IN 运算符和 UNNEST。如需检查数组是否包含与某条件匹配的值,请结合使用 EXISTS 函数和 UNNEST。扫描特定值如需扫描数组中的特定值,请将 IN 运算符与 UNNEST 结合使用。示例以下示例在数组中包含数字 2 时返回 true。SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;+----------------+| contains_value |+----原创 2020-07-22 16:57:49 · 285 阅读 · 0 评论 -
sql 过滤数组
过滤数组以下示例在 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原创 2020-07-22 16:52:13 · 896 阅读 · 0 评论