初见 ByConity

ssh -p 23 <提供的用户名>@<ECS服务器IP地址>
例如：ssh -p 23 root@14.103.145.182

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

tmux new -s $user_id
例如：tmux new -s user001
注：$user_id是可以自定义的会话名称

clickhouse client --port 9010

with ws as        (select d_year AS ws_sold_year, ws_item_sk,        ws_bill_customer_sk ws_customer_sk,        sum(ws_quantity) ws_qty,        sum(ws_wholesale_cost) ws_wc,        sum(ws_sales_price) ws_sp        from web_sales        left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk        join date_dim on ws_sold_date_sk = d_date_sk        where wr_order_number is null        group by d_year, ws_item_sk, ws_bill_customer_sk        ),        cs as        (select d_year AS cs_sold_year, cs_item_sk,        cs_bill_customer_sk cs_customer_sk,        sum(cs_quantity) cs_qty,        sum(cs_wholesale_cost) cs_wc,        sum(cs_sales_price) cs_sp        from catalog_sales        left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk        join date_dim on cs_sold_date_sk = d_date_sk        where cr_order_number is null        group by d_year, cs_item_sk, cs_bill_customer_sk        ),        ss as        (select d_year AS ss_sold_year, ss_item_sk,        ss_customer_sk,        sum(ss_quantity) ss_qty,        sum(ss_wholesale_cost) ss_wc,        sum(ss_sales_price) ss_sp        from store_sales        left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk        join date_dim on ss_sold_date_sk = d_date_sk        where sr_ticket_number is null        group by d_year, ss_item_sk, ss_customer_sk        )        select        ss_sold_year, ss_item_sk, ss_customer_sk,        round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,        ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,        coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,        coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,        coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price        from ss        left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)        left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)        where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000        order by        ss_sold_year, ss_item_sk, ss_customer_sk,        ss_qty desc, ss_wc desc, ss_sp desc,        other_chan_qty,        other_chan_wholesale_cost,        other_chan_sales_price,        ratio        LIMIT 100;

with cs_ui as        (select cs_item_sk        ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund        from catalog_sales        ,catalog_returns        where cs_item_sk = cr_item_sk        and cs_order_number = cr_order_number        group by cs_item_sk        having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),        cross_sales as        (select i_product_name product_name        ,i_item_sk item_sk        ,s_store_name store_name        ,s_zip store_zip        ,ad1.ca_street_number b_street_number        ,ad1.ca_street_name b_street_name        ,ad1.ca_city b_city        ,ad1.ca_zip b_zip        ,ad2.ca_street_number c_street_number        ,ad2.ca_street_name c_street_name        ,ad2.ca_city c_city        ,ad2.ca_zip c_zip        ,d1.d_year as syear        ,d2.d_year as fsyear        ,d3.d_year s2year        ,count(*) cnt        ,sum(ss_wholesale_cost) s1        ,sum(ss_list_price) s2        ,sum(ss_coupon_amt) s3        FROM   store_sales        ,store_returns        ,cs_ui        ,date_dim d1        ,date_dim d2        ,date_dim d3        ,store        ,customer        ,customer_demographics cd1        ,customer_demographics cd2        ,promotion        ,household_demographics hd1        ,household_demographics hd2        ,customer_address ad1        ,customer_address ad2        ,income_band ib1        ,income_band ib2        ,item        WHERE  ss_store_sk = s_store_sk AND        ss_sold_date_sk = d1.d_date_sk AND        ss_customer_sk = c_customer_sk AND        ss_cdemo_sk= cd1.cd_demo_sk AND        ss_hdemo_sk = hd1.hd_demo_sk AND        ss_addr_sk = ad1.ca_address_sk and        ss_item_sk = i_item_sk and        ss_item_sk = sr_item_sk and        ss_ticket_number = sr_ticket_number and        ss_item_sk = cs_ui.cs_item_sk and        c_current_cdemo_sk = cd2.cd_demo_sk AND        c_current_hdemo_sk = hd2.hd_demo_sk AND        c_current_addr_sk = ad2.ca_address_sk and        c_first_sales_date_sk = d2.d_date_sk and        c_first_shipto_date_sk = d3.d_date_sk and        ss_promo_sk = p_promo_sk and        hd1.hd_income_band_sk = ib1.ib_income_band_sk and        hd2.hd_income_band_sk = ib2.ib_income_band_sk and        cd1.cd_marital_status <> cd2.cd_marital_status and        i_color in ('purple','burlywood','indian','spring','floral','medium') and        i_current_price between 64 and 64 + 10 and        i_current_price between 64 + 1 and 64 + 15        group by i_product_name        ,i_item_sk        ,s_store_name        ,s_zip        ,ad1.ca_street_number        ,ad1.ca_street_name        ,ad1.ca_city        ,ad1.ca_zip        ,ad2.ca_street_number        ,ad2.ca_street_name        ,ad2.ca_city        ,ad2.ca_zip        ,d1.d_year        ,d2.d_year        ,d3.d_year        )        select cs1.product_name        ,cs1.store_name        ,cs1.store_zip        ,cs1.b_street_number        ,cs1.b_street_name        ,cs1.b_city        ,cs1.b_zip        ,cs1.c_street_number        ,cs1.c_street_name        ,cs1.c_city        ,cs1.c_zip        ,cs1.syear        ,cs1.cnt        ,cs1.s1 as s11        ,cs1.s2 as s21        ,cs1.s3 as s31        ,cs2.s1 as s12        ,cs2.s2 as s22        ,cs2.s3 as s32        ,cs2.syear        ,cs2.cnt        from cross_sales cs1,cross_sales cs2        where cs1.item_sk=cs2.item_sk and        cs1.syear = 1999 and        cs2.syear = 1999 + 1 and        cs2.cnt <= cs1.cnt and        cs1.store_name = cs2.store_name and        cs1.store_zip = cs2.store_zip        order by cs1.product_name        ,cs1.store_name        ,cs2.cnt        ,cs1.s1        ,cs2.s1;