sql,Code片段,代码实例,代码示例,代码片段库

sql 泰国PageView按房产类型

th-pageview-propertytype.sql

WITH seeker AS (
SELECT
DATE_TRUNC(CAST(CONCAT(CONCAT(CONCAT(SUBSTR(table_id,1,4),'-'),CONCAT(SUBSTR(table_id,5,2),'-')),SUBSTR(table_id,7,2)) AS DATE), MONTH) AS date,
custom_listing_id, 
SUM(IF(custom_page_type = "Listing Detail" AND hit_type = "PAGE", 1, 0)) AS listing_page_view
FROM
  `propertyguru-datalake-v0.ods.th_seeker_events_denormed_v2`
WHERE
  _PARTITIONTIME >= "2018-06-01 00:00:00"
  AND _PARTITIONTIME < "2019-07-01 00:00:00"
  AND custom_listing_id != "0"
GROUP BY 1,2
),

listing_table AS (
SELECT
id AS listing_id,
property_type_code,
region_code,
district_code
FROM 
  (SELECT id, property_type_code, region_code, district_code, RANK() OVER(PARTITION BY id ORDER BY time_update_last DESC) AS times 
  FROM `propertyguru-datalake-v0.production_dd_db05_propertydb_th.v_listing`
  )
WHERE times = 1
GROUP BY 1,2,3,4
),

property_table AS (
SELECT
property_type_code,
property_type_name
FROM
  `propertyguru-datalake-v0.ods.dim_property_type`
WHERE country = "TH"
),

geo_table AS (
SELECT
region_code,
region,
district_code,
district
FROM
  `propertyguru-datalake-v0.location_insight.th_geographic_information`
),

detailed_listing_table AS (
SELECT
listing_id,
listing.property_type_code,
property_type_name,
listing.region_code,
region,
listing.district_code,
district
FROM listing_table listing
LEFT JOIN property_table pro
ON listing.property_type_code = pro.property_type_code
LEFT JOIN geo_table geo 
ON listing.region_code = geo.region_code AND listing.district_code = geo.district_code
GROUP BY 1,2,3,4,5,6,7
),

final_table AS (
SELECT
date,
listing_id, 
property_type_name,
region,
district,
listing_page_view
FROM seeker
LEFT JOIN detailed_listing_table
ON CAST(detailed_listing_table.listing_id AS STRING) = seeker.custom_listing_id
)

SELECT
date,
region,
district,
property_type_name,
SUM(IF(listing_page_view IS NULL, 0, listing_page_view)) AS page_view
FROM final_table 
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4

sql 聚合函数

agg.sql

SELECT MAX age FROM abc;                -- Same for MIN

SELECT AVG (Field_Name) FROM Table_Name;    -- Returns Average

SELECT COUNT * FROM Table_Name;

sql 清除数据扩展名

如果您需要一种自动清除数据扩展的方法，则可以简单地使用执行覆盖的SQL查询。如果在目标中指定任何必需的列，则查询不需要from子句。 例如，如果目标数据扩展中需要EmailAddress，只需将该值设置为null并添加别名：

clearDE.sql

select
null as Emailaddress
where 0 = 1
/* action: overwrite */

sql 清除数据扩展名

如果您需要一种自动清除数据扩展的方法，则可以简单地使用执行覆盖的SQL查询。如果在目标中指定任何必需的列，则查询不需要from子句。 例如，如果目标数据扩展中需要EmailAddress，只需将该值设置为null并添加别名：

clearDE.sql

select
null as Emailaddress
where 0 = 1
/* action: overwrite */

sql 聚合

Query.sql

-- Finding out how many males and females there are in the database
SELECT COUNT(sex), sex FROM employee GROUP BY sex;

-- Finding out the total amount of sales each employee has made 
SELECT SUM(total_sales), emp_id FROM works_with GROUP BY emp_id;

sql 填充表格

Query.Sql

-- Corporate Branch
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

UPDATE employee 
SET branch_id = 1
WHERE emp_id = 100;

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 111000, 100, 1);

-- Scranton Branch
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;

INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);

-- Stamford Branch
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);

-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');

-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES(401, 'Lackawana Country', 2);
INSERT INTO client VALUES(402, 'FedEx', 3);
INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES(405, 'Times Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);

-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);

sql 公司数据库架构

Query.sql

-- Creating the employee table and defining its columns and their properties
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR (40),
    birth_day DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);
-- Creating the branch table and defining its columns and their properties
CREATE TABLE branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_date DATE,
    FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

-- Creating the branch table and defining its columns and their properties
CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(40),
    branch_id INT,
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

-- Creating the branch table and defining its columns and their properties
CREATE TABLE works_with (
    emp_id INT,
    client_id INT,
    total_sales INT,
    PRIMARY KEY(emp_id, client_id),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
    FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

-- Creating the branch table and defining its columns and their properties
CREATE TABLE branch_supplier (
    branch_id INT,
    supplier_name VARCHAR(40),
    supply_type VARCHAR(40),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

-- Adding the foreign keys to the employee table after the branch table has been created
ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee 
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

sql 设置密码

##在客户或候选人登录名上设置密码为“Passord1” 这些脚本会将所有联系人登录的密码设置为“Passord1”。 如果您需要在测试环境中登录其配置文件，请将where语句添加到脚本中，如果您只想重置一个密码。 snd脚本会将候选登录的密码更改为“Passord1”。 are语句在此脚本中，因为如果您在没有脚本的情况下运行它将使用最多** _分钟_ **来完成，因为触发器位于KANDIDAT表上。我不建议更新所有候选登录，除非它是有原因的。

SetPassord

update ContactLogin
  set PasswordHash = 'UiAGf9DlWF+/Yvh9wTVPFzzHHNG8TcoWdRm0Dr6Jfo7wHAbH/Ttz+1hkK3jc1UCmLZMlaWiolfDzbGtKJP4nIhYhQp/WmWo3urLQBOLij7gb0QCT+YPsBnfY0ukCjY99LPOFf+K8PlSfqi+2E6ZkHczttEJqgwZ1xFo7Bw68mJs=',
      Salt = 'aTUN7Usa0iB2ATzIEb7tKCuJjhT/qtXUS1f4laoqlKtX4Sat9Pah1e+syb1ws+xbTJr2ub3wYmOZ8JDqGFydYoABh2p9c5yywTXXtoojBmN9tbWTrfv0ide3R9YKucJEOlYiz0PCO7FoZEKCx77zxYArZjzSAA9c0bUKQze+01E='

Candidate login

update KANDIDAT
set PASSWDHASH = 'UiAGf9DlWF+/Yvh9wTVPFzzHHNG8TcoWdRm0Dr6Jfo7wHAbH/Ttz+1hkK3jc1UCmLZMlaWiolfDzbGtKJP4nIhYhQp/WmWo3urLQBOLij7gb0QCT+YPsBnfY0ukCjY99LPOFf+K8PlSfqi+2E6ZkHczttEJqgwZ1xFo7Bw68mJs=',
SALT = 'aTUN7Usa0iB2ATzIEb7tKCuJjhT/qtXUS1f4laoqlKtX4Sat9Pah1e+syb1ws+xbTJr2ub3wYmOZ8JDqGFydYoABh2p9c5yywTXXtoojBmN9tbWTrfv0ide3R9YKucJEOlYiz0PCO7FoZEKCx77zxYArZjzSAA9c0bUKQze+01E='
where ID = 461983

sql 课程模块

##插入脚本到课程表 使用此方法将'虚拟'数据插入课程 这将在JM的课程概述页面中添加一些数据。如果你愿意，你可以批准候选人。编辑consultantId变量值以将信息设置为您自己的用户。

InsertDummyCourseData

declare @ConsultantId

set @ConsultantId = 801

INSERT INTO Course.CandidateCompetence
VALUES (ConsultantId, '20180215 10:00:00 AM', null, null, 328369),
(ConsultantId, '20180210 10:00:00 AM', null, null, 225250),
(ConsultantId, '20180210 10:00:00 AM', null, null, 419014),
(ConsultantId, '20180210 10:00:00 AM', null, null, 59027),
(ConsultantId, '20180218 10:00:00 AM', null, null, 66636),
(ConsultantId, '20180418 10:00:00 AM', null, null, 15058),
(ConsultantId, '20180418 10:00:00 AM', null, null, 72441),
(ConsultantId, '20180418 10:00:00 AM', null, null, 328369),
(ConsultantId, '20180615 06:00:00 PM', null, null, 328369)

INSERT INTO Course.CandidateCompetenceSub
VALUES (1, 2), (2, 2), (3, 3), (4, 2), (5, 3), (6, 2), (7, 4), (7, 5), (8, 10), (8, 11), (8, 12), (9, 2)

INSERT INTO Course.CourseLicenseReservation
VALUES
  (ConsultantId, '20180130 02:22:00 PM', 3, 1),
  (ConsultantId, '20180130 02:22:00 PM', 3, 2),
  (ConsultantId, '20180130 02:22:00 PM', 2, 3),
  (ConsultantId, '20180130 02:22:00 PM', 3, 4)
  
INSERT INTO Course.CandidateCourse
VALUES
  (1, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 1, 328369, null, null, 1, 0),
  (5, ConsultantId, '20170718 10:00:00 AM', null, null, null, null, null, null, 328369, null, null, null, 0),
  (1, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 2, 225250, null, null, 1, 0),
  (4, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 3, 419014, 1, null, null, 0),
  (5, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 4, 59027, null, null, null, 0),
  (4, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 5, 66636, 2, null, null, 0),
  (1, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 6, 15058, null, null, 1, 0),
  (6, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 7, 72441, null, null, 2, 0),
  (2, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', '20170918 10:00:00 AM', null, null, null, 8, 328369, null, null, null, 0),
  (5, ConsultantId, '20170718 10:00:00 AM', null, null, null, null, null, null, 347790, null, null, null, 0),
  (6, ConsultantId, '20170718 10:00:00 AM', null, null, null, null, null, null, 65043, null, null, 2, 0),
  (4, ConsultantId, '20170718 10:00:00 AM', null, null, null, null, null, null, 43144, 3, null, 2, 0),
  (2, ConsultantId, '20170718 10:00:00 AM', null, null, null, null, null, null, 20514, null, null, null, 0),
  (2, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 225250, null, null, null, 0),
  (3, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 419014, null, null, null, 0),
  (1, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 59027, null, null, null, 0),
  (2, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 66636, null, null, null, 0),
  (3, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 15058, null, null, null, 0),
  (4, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 72441, 4, null, null, 0),
  (3, ConsultantId, '20170718 10:00:00 AM', '20170818 10:00:00 AM', null, null, null, null, null, 328369, null, null, null, 0),
  (1, ConsultantId, '20180112 10:00:00 AM', '20180113 06:00:00 PM', '20180113 06:00:00 PM', null, null, null, 9, 328369, null, null, 1, 0)

sql UpdateAllowedVersion JM

##运行以在DB中设置允许的版本 数据库中的“AllowedVersions”值确定客户端是否需要更新其JM。 但这对LAB不起作用，这就是为什么我们可以使用它来设置适合实验室当前版本的值。 如果您需要更新超过最后两位数字，请更新脚本。

UpdateAllowedVersion JM

update LANG
set TEXT = '255.11.2144'
where KEYCODE = 'AllowedVersions'