Database nesting queries

Source: Internet
Author: User

:) Off-Topic

Long time no writing essays recently always for other things to worry about reverse telecommuting emotional problems and mood is low and so bad mood is not easy to get sick yesterday burned a day well all over the family is my all want to support me and bring me a large part of the pressure of the program Monkey Baby or a lot of improve themselves bar (old?????? ) Old

Select t1.*,
Case when coalesce (T2. score,0) = 0 Then 0 else
round (t1. Score-t2.score)/t2. score*100,2) End SCORE_HB,//Condition: The current field is not empty and is not 0 o'clock, you can perform a chain operation
Case when COALESCE (T2. analyze_delay,0) = 0 Then 0 else
Round (t1. Analyze_delay-t2.analyze_delay)/t2. analyze_delay*100,2) End ANALYZE_DELAY_HB,
Case when COALESCE (T2. link_delay,0) = 0 Then 0 else
Round (t1. Link_delay-t2.link_delay)/t2. link_delay*100,2) End LINK_DELAY_HB,
Case when COALESCE (T2. first_get_delay,0) = 0 Then 0 else
Round (t1. First_get_delay-t2.first_get_delay)/t2. first_get_delay*100,2) End FIRST_GET_DELAY_HB,
Case when COALESCE (T2. first_page_delay,0) = 0 Then 0 else
Round (t1. First_page_delay-t2.first_page_delay)/t2. first_page_delay*100,2) End FIRST_PAGE_DELAY_HB,
Case when COALESCE (T2. download_rate,0) = 0 Then 0 else
Round (t1. Download_rate-t2.download_rate)/t2. download_rate*100,2) End DOWNLOAD_RATE_HB,
Case when COALESCE (T2. webload_delay,0) = 0 Then 0 else
Round (t1. Webload_delay-t2.webload_delay)/t2. webload_delay*100,2) End WEBLOAD_DELAY_HB,
Case when COALESCE (T2. total_delay,0) = 0 Then 0 else
Round (t1. Total_delay-t2.total_delay)/t2. total_delay*100,2) End TOTAL_DELAY_HB,
Case when COALESCE (T2. download_delay,0) = 0 Then 0 else
Round (t1. Download_delay-t2.download_delay)/t2. download_delay*100,2) End DOWNLOAD_DELAY_HB,
Case when COALESCE (T2. analyze_suc_rate,0) = 0 Then 0 else
Round (t1. Analyze_suc_rate-t2.analyze_suc_rate)/t2. analyze_suc_rate*100,2) End ANALYZE_SUC_RATE_HB,
Case when COALESCE (T2. link_suc_delay,0) = 0 Then 0 else
Round (t1. Link_suc_delay-t2.link_suc_delay)/t2. link_suc_delay*100,2) End LINK_SUC_DELAY_HB,
Case when COALESCE (T2. download_size,0) = 0 Then 0 else
Round (t1. Download_size-t2.download_size)/t2. download_size*100,2) End DOWNLOAD_SIZE_HB,
Case when COALESCE (T2. useable_rate,0) = 0 Then 0 else
Round (t1. Useable_rate-t2.useable_rate)/t2. useable_rate*100,2) End USEABLE_RATE_HB
From


(SELECT
Day_key,
City_key,
Oper_key,
App_type_key,
App_subtype_key,
Host_key,
round (score,2) as score,//To define aliases for data columns after any processing
Analyze_delay,
Link_delay,
First_get_delay,
First_page_delay,
Download_rate,
Webload_delay,
Total_delay,
Download_delay,
Round (analyze_suc_rate,2) as Analyze_suc_rate,
Link_suc_delay,
Download_size,
Round (useable_rate,2) as Useable_rate,
Num
From Dw_ft_web_city_l2_dy
where 1=1
$[and City_key = (: {city})]
$[and Day_key = (: {time})]) t1

Left join //Correlation query
(
Select
Day_key,
City_key,
Oper_key,
App_type_key,
App_subtype_key,
Host_key,
Round (score,2) as score,
Analyze_delay,
Link_delay,
First_get_delay,
First_page_delay,
Download_rate,
Webload_delay,
Total_delay,
Download_delay,
Round (analyze_suc_rate,2) as Analyze_suc_rate,
Link_suc_delay,
Download_size,
Round (useable_rate,2) as Useable_rate,
Num
From Dw_ft_web_city_l2_dy
where 1=1
$[and City_key = (: {city})]
$[and Day_key = To_number (To_char (To_date (': {time} ', ' YyyyMMdd ')-1, ' YYYYMMDD ')]) T2
on T1. City_key=t2. City_key and T1. Oper_key=t2. Oper_key

Database nesting queries

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.