statistics_level 参数的应用

本文介绍了Oracle数据库中statistics_level参数的作用及使用方法。该参数用于控制性能统计信息的收集级别,包含basic、typical和all三种设置。文章详细解释了不同设置下收集的统计信息种类,并提供了设置示例。
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够挣断99%的性能问题了.
example: seting statistics_level
alter system set statistics_level=basic;
alter system set statistics_level=typical;
alter system set statistics_level=all;
or
alter session set statistics_level=basic;
alter session set statistics_level=typical;
alter session set statistics_level=all;
oracle 还提供了v$statistics_level视图用来记录各个参数下oracle可以收集的统计信息的类别:
SQL> ALTER SYSTEM SET statistics_level=basic;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics DISABLED DISABLED TYPICAL YES
8 rows selected.
statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;
SQL> ALTER SYSTEM SET statistics_level=typical;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
8 rows selected.
statistics_level=typical的时
候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;

SQL> ALTER SYSTEM SET statistics_level=all;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics ENABLED ENABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics ENABLED ENABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
如果当statistics_level=ALL,系统收集所有的统计信息.
/* * Copyright (c) 2012 The WebRTC project authors. All Rights Reserved. * * Use of this source code is governed by a BSD-style license * that can be found in the LICENSE file in the root of the source * tree. An additional intellectual property rights grant can be found * in the file PATENTS. All contributing project authors may * be found in the AUTHORS file in the root of the source tree. */ #include "modules/audio_coding/neteq/delay_manager.h" #include <assert.h> #include <stdio.h> #include <stdlib.h> #include <algorithm> #include <memory> #include <numeric> #include <string> #include "modules/audio_coding/neteq/histogram.h" #include "modules/include/module_common_types_public.h" #include "rtc_base/checks.h" #include "rtc_base/logging.h" #include "rtc_base/numerics/safe_conversions.h" #include "rtc_base/numerics/safe_minmax.h" #include "system_wrappers/include/field_trial.h" namespace { constexpr int kMinBaseMinimumDelayMs = 0; constexpr int kMaxBaseMinimumDelayMs = 10000; constexpr int kMaxReorderedPackets = 10; // Max number of consecutive reordered packets. constexpr int kMaxHistoryMs = 2000; // Oldest packet to include in history to // calculate relative packet arrival delay. constexpr int kDelayBuckets = 100; constexpr int kBucketSizeMs = 20; constexpr int kDecelerationTargetLevelOffsetMs = 85 << 8; // In Q8. int PercentileToQuantile(double percentile) { return static_cast<int>((1 << 30) * percentile / 100.0 + 0.5); } struct DelayHistogramConfig { int quantile = 1041529569; // 0.97 in Q30. int forget_factor = 32745; // 0.9993 in Q15. absl::optional<double> start_forget_weight = 2; }; DelayHistogramConfig GetDelayHistogramConfig() { constexpr char kDelayHistogramFieldTrial[] = "WebRTC-Audio-NetEqDelayHistogram"; DelayHistogramConfig config; if (webrtc::field_trial::IsEnabled(kDelayHistogramFieldTrial)) { const auto field_trial_string = webrtc::field_trial::FindFullName(kDelayHistogramFieldTrial); double percentile = -1.0; double forget_factor = -1.0; double start_forget_weight = -1.0; if (sscanf(field_trial_string.c_str(), "Enabled-%lf-%lf-%lf", &percentile, &forget_factor, &start_forget_weight) >= 2 && percentile >= 0.0 && percentile <= 100.0 && forget_factor >= 0.0 && forget_factor <= 1.0) { config.quantile = PercentileToQuantile(percentile); config.forget_factor = (1 << 15) * forget_factor; config.start_forget_weight = start_forget_weight >= 1 ? absl::make_optional(start_forget_weight) : absl::nullopt; } } RTC_LOG(LS_INFO) << "Delay histogram config:" " quantile=" << config.quantile << " forget_factor=" << config.forget_factor << " start_forget_weight=" << config.start_forget_weight.value_or(0); return config; } } // namespace namespace webrtc { DelayManager::DelayManager(size_t max_packets_in_buffer, int base_minimum_delay_ms, int histogram_quantile, bool enable_rtx_handling, const TickTimer* tick_timer, std::unique_ptr<Histogram> histogram) : first_packet_received_(false), max_packets_in_buffer_(max_packets_in_buffer), histogram_(std::move(histogram)), histogram_quantile_(histogram_quantile), tick_timer_(tick_timer), base_minimum_delay_ms_(base_minimum_delay_ms), effective_minimum_delay_ms_(base_minimum_delay_ms), base_target_level_(4), // In Q0 domain. target_level_(base_target_level_ << 8), // In Q8 domain. packet_len_ms_(0), last_seq_no_(0), last_timestamp_(0), minimum_delay_ms_(0), maximum_delay_ms_(0), last_pack_cng_or_dtmf_(1), enable_rtx_handling_(enable_rtx_handling) { RTC_CHECK(histogram_); RTC_DCHECK_GE(base_minimum_delay_ms_, 0); Reset(); } std::unique_ptr<DelayManager> DelayManager::Create( size_t max_packets_in_buffer, int base_minimum_delay_ms, bool enable_rtx_handling, const TickTimer* tick_timer) { DelayHistogramConfig config = GetDelayHistogramConfig(); const int quantile = config.quantile; std::unique_ptr<Histogram> histogram = std::make_unique<Histogram>( kDelayBuckets, config.forget_factor, config.start_forget_weight); return std::make_unique<DelayManager>( max_packets_in_buffer, base_minimum_delay_ms, quantile, enable_rtx_handling, tick_timer, std::move(histogram)); } DelayManager::~DelayManager() {} absl::optional<int> DelayManager::Update(uint16_t sequence_number, uint32_t timestamp, int sample_rate_hz) { if (sample_rate_hz <= 0) { return absl::nullopt; } if (!first_packet_received_) { // Prepare for next packet arrival. packet_iat_stopwatch_ = tick_timer_->GetNewStopwatch(); last_seq_no_ = sequence_number; last_timestamp_ = timestamp; first_packet_received_ = true; return absl::nullopt; } // Try calculating packet length from current and previous timestamps. int packet_len_ms; if (!IsNewerTimestamp(timestamp, last_timestamp_) || !IsNewerSequenceNumber(sequence_number, last_seq_no_)) { // Wrong timestamp or sequence order; use stored value. packet_len_ms = packet_len_ms_; } else { // Calculate timestamps per packet and derive packet length in ms. int64_t packet_len_samp = static_cast<uint32_t>(timestamp - last_timestamp_) / static_cast<uint16_t>(sequence_number - last_seq_no_); packet_len_ms = rtc::saturated_cast<int>(1000 * packet_len_samp / sample_rate_hz); } bool reordered = false; absl::optional<int> relative_delay; if (packet_len_ms > 0) { // Cannot update statistics unless |packet_len_ms| is valid. // Inter-arrival time (IAT) in integer "packet times" (rounding down). This // is the value added to the inter-arrival time histogram. int iat_ms = packet_iat_stopwatch_->ElapsedMs(); // Check for discontinuous packet sequence and re-ordering. if (IsNewerSequenceNumber(sequence_number, last_seq_no_ + 1)) { // Compensate for gap in the sequence numbers. Reduce IAT with the // expected extra time due to lost packets. int packet_offset = static_cast<uint16_t>(sequence_number - last_seq_no_ - 1); iat_ms -= packet_offset * packet_len_ms; } else if (!IsNewerSequenceNumber(sequence_number, last_seq_no_)) { int packet_offset = static_cast<uint16_t>(last_seq_no_ + 1 - sequence_number); iat_ms += packet_offset * packet_len_ms; reordered = true; } int iat_delay = iat_ms - packet_len_ms; if (reordered) { relative_delay = std::max(iat_delay, 0); } else { UpdateDelayHistory(iat_delay, timestamp, sample_rate_hz); relative_delay = CalculateRelativePacketArrivalDelay(); } const int index = relative_delay.value() / kBucketSizeMs; if (index < histogram_->NumBuckets()) { // Maximum delay to register is 2000 ms. histogram_->Add(index); } // Calculate new |target_level_| based on updated statistics. target_level_ = CalculateTargetLevel(); LimitTargetLevel(); } // End if (packet_len_ms > 0). if (enable_rtx_handling_ && reordered && num_reordered_packets_ < kMaxReorderedPackets) { ++num_reordered_packets_; return relative_delay; } num_reordered_packets_ = 0; // Prepare for next packet arrival. packet_iat_stopwatch_ = tick_timer_->GetNewStopwatch(); last_seq_no_ = sequence_number; last_timestamp_ = timestamp; return relative_delay; } void DelayManager::UpdateDelayHistory(int iat_delay_ms, uint32_t timestamp, int sample_rate_hz) { PacketDelay delay; delay.iat_delay_ms = iat_delay_ms; delay.timestamp = timestamp; delay_history_.push_back(delay); while (timestamp - delay_history_.front().timestamp > static_cast<uint32_t>(kMaxHistoryMs * sample_rate_hz / 1000)) { delay_history_.pop_front(); } } int DelayManager::CalculateRelativePacketArrivalDelay() const { // This effectively calculates arrival delay of a packet relative to the // packet preceding the history window. If the arrival delay ever becomes // smaller than zero, it means the reference packet is invalid, and we // move the reference. int relative_delay = 0; for (const PacketDelay& delay : delay_history_) { relative_delay += delay.iat_delay_ms; relative_delay = std::max(relative_delay, 0); } return relative_delay; } // Enforces upper and lower limits for |target_level_|. The upper limit is // chosen to be minimum of i) 75% of |max_packets_in_buffer_|, to leave some // headroom for natural fluctuations around the target, and ii) equivalent of // |maximum_delay_ms_| in packets. Note that in practice, if no // |maximum_delay_ms_| is specified, this does not have any impact, since the // target level is far below the buffer capacity in all reasonable cases. // The lower limit is equivalent of |effective_minimum_delay_ms_| in packets. // We update |least_required_level_| while the above limits are applied. // TODO(hlundin): Move this check to the buffer logistics class. void DelayManager::LimitTargetLevel() { if (packet_len_ms_ > 0 && effective_minimum_delay_ms_ > 0) { int minimum_delay_packet_q8 = (effective_minimum_delay_ms_ << 8) / packet_len_ms_; target_level_ = std::max(target_level_, minimum_delay_packet_q8); } if (maximum_delay_ms_ > 0 && packet_len_ms_ > 0) { int maximum_delay_packet_q8 = (maximum_delay_ms_ << 8) / packet_len_ms_; target_level_ = std::min(target_level_, maximum_delay_packet_q8); } // Shift to Q8, then 75%.; int max_buffer_packets_q8 = static_cast<int>((3 * (max_packets_in_buffer_ << 8)) / 4); target_level_ = std::min(target_level_, max_buffer_packets_q8); // Sanity check, at least 1 packet (in Q8). target_level_ = std::max(target_level_, 1 << 8); } int DelayManager::CalculateTargetLevel() { int limit_probability = histogram_quantile_; int bucket_index = histogram_->Quantile(limit_probability); int target_level = 1; if (packet_len_ms_ > 0) { target_level += bucket_index * kBucketSizeMs / packet_len_ms_; } base_target_level_ = target_level; // Sanity check. |target_level| must be strictly positive. target_level = std::max(target_level, 1); // Scale to Q8 and assign to member variable. target_level_ = target_level << 8; return target_level_; } int DelayManager::SetPacketAudioLength(int length_ms) { if (length_ms <= 0) { RTC_LOG_F(LS_ERROR) << "length_ms = " << length_ms; return -1; } packet_len_ms_ = length_ms; packet_iat_stopwatch_ = tick_timer_->GetNewStopwatch(); last_pack_cng_or_dtmf_ = 1; // TODO(hlundin): Legacy. Remove? return 0; } void DelayManager::Reset() { packet_len_ms_ = 0; // Packet size unknown. histogram_->Reset(); delay_history_.clear(); base_target_level_ = 4; target_level_ = base_target_level_ << 8; packet_iat_stopwatch_ = tick_timer_->GetNewStopwatch(); last_pack_cng_or_dtmf_ = 1; } void DelayManager::ResetPacketIatCount() { packet_iat_stopwatch_ = tick_timer_->GetNewStopwatch(); } void DelayManager::BufferLimits(int* lower_limit, int* higher_limit) const { BufferLimits(target_level_, lower_limit, higher_limit); } // Note that |low_limit| and |higher_limit| are not assigned to // |minimum_delay_ms_| and |maximum_delay_ms_| defined by the client of this // class. They are computed from |target_level| in Q8 and used for decision // making. void DelayManager::BufferLimits(int target_level, int* lower_limit, int* higher_limit) const { if (!lower_limit || !higher_limit) { RTC_LOG_F(LS_ERROR) << "NULL pointers supplied as input"; assert(false); return; } // |target_level| is in Q8 already. *lower_limit = (target_level * 3) / 4; if (packet_len_ms_ > 0) { *lower_limit = std::max(*lower_limit, target_level - kDecelerationTargetLevelOffsetMs / packet_len_ms_); } int window_20ms = 0x7FFF; // Default large value for legacy bit-exactness. if (packet_len_ms_ > 0) { window_20ms = (20 << 8) / packet_len_ms_; } // |higher_limit| is equal to |target_level|, but should at // least be 20 ms higher than |lower_limit|. *higher_limit = std::max(target_level, *lower_limit + window_20ms); } int DelayManager::TargetLevel() const { return target_level_; } void DelayManager::LastDecodedWasCngOrDtmf(bool it_was) { if (it_was) { last_pack_cng_or_dtmf_ = 1; } else if (last_pack_cng_or_dtmf_ != 0) { last_pack_cng_or_dtmf_ = -1; } } void DelayManager::RegisterEmptyPacket() { ++last_seq_no_; } bool DelayManager::IsValidMinimumDelay(int delay_ms) const { return 0 <= delay_ms && delay_ms <= MinimumDelayUpperBound(); } bool DelayManager::IsValidBaseMinimumDelay(int delay_ms) const { return kMinBaseMinimumDelayMs <= delay_ms && delay_ms <= kMaxBaseMinimumDelayMs; } bool DelayManager::SetMinimumDelay(int delay_ms) { if (!IsValidMinimumDelay(delay_ms)) { return false; } minimum_delay_ms_ = delay_ms; UpdateEffectiveMinimumDelay(); return true; } bool DelayManager::SetMaximumDelay(int delay_ms) { // If |delay_ms| is zero then it unsets the maximum delay and target level is // unconstrained by maximum delay. if (delay_ms != 0 && (delay_ms < minimum_delay_ms_ || delay_ms < packet_len_ms_)) { // Maximum delay shouldn't be less than minimum delay or less than a packet. return false; } maximum_delay_ms_ = delay_ms; UpdateEffectiveMinimumDelay(); return true; } bool DelayManager::SetBaseMinimumDelay(int delay_ms) { if (!IsValidBaseMinimumDelay(delay_ms)) { return false; } base_minimum_delay_ms_ = delay_ms; UpdateEffectiveMinimumDelay(); return true; } int DelayManager::GetBaseMinimumDelay() const { return base_minimum_delay_ms_; } int DelayManager::base_target_level() const { return base_target_level_; } int DelayManager::last_pack_cng_or_dtmf() const { return last_pack_cng_or_dtmf_; } void DelayManager::set_last_pack_cng_or_dtmf(int value) { last_pack_cng_or_dtmf_ = value; } void DelayManager::UpdateEffectiveMinimumDelay() { // Clamp |base_minimum_delay_ms_| into the range which can be effectively // used. const int base_minimum_delay_ms = rtc::SafeClamp(base_minimum_delay_ms_, 0, MinimumDelayUpperBound()); effective_minimum_delay_ms_ = std::max(minimum_delay_ms_, base_minimum_delay_ms); } int DelayManager::MinimumDelayUpperBound() const { // Choose the lowest possible bound discarding 0 cases which mean the value // is not set and unconstrained. int q75 = MaxBufferTimeQ75(); q75 = q75 > 0 ? q75 : kMaxBaseMinimumDelayMs; const int maximum_delay_ms = maximum_delay_ms_ > 0 ? maximum_delay_ms_ : kMaxBaseMinimumDelayMs; return std::min(maximum_delay_ms, q75); } int DelayManager::MaxBufferTimeQ75() const { const int max_buffer_time = max_packets_in_buffer_ * packet_len_ms_; return rtc::dchecked_cast<int>(3 * max_buffer_time / 4); } } // namespace webrtc 解释在干嘛
最新发布
10-12
WITH base_data AS ( SELECT level0.simple_name AS level0_simple_name, level1.simple_name AS level1_simple_name, level2.simple_name AS level2_simple_name, level2.id AS level2_id, mdc.name AS device_name, enh.year, enh.month, enh.day, hours.hour, -- 计算每小时能耗值(考虑加减操作) SUM( CASE hours.hour WHEN 0 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h0 ELSE -enh.h0 END WHEN 1 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h1 ELSE -enh.h1 END WHEN 2 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h2 ELSE -enh.h2 END WHEN 3 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h3 ELSE -enh.h3 END WHEN 4 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h4 ELSE -enh.h4 END WHEN 5 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h5 ELSE -enh.h5 END WHEN 6 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h6 ELSE -enh.h6 END WHEN 7 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h7 ELSE -enh.h7 END WHEN 8 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h8 ELSE -enh.h8 END WHEN 9 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h9 ELSE -enh.h9 END WHEN 10 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h10 ELSE -enh.h10 END WHEN 11 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h11 ELSE -enh.h11 END WHEN 12 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h12 ELSE -enh.h12 END WHEN 13 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h13 ELSE -enh.h13 END WHEN 14 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h14 ELSE -enh.h14 END WHEN 15 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h15 ELSE -enh.h15 END WHEN 16 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h16 ELSE -enh.h16 END WHEN 17 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h17 ELSE -enh.h17 END WHEN 18 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h18 ELSE -enh.h18 END WHEN 19 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h19 ELSE -enh.h19 END WHEN 20 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h20 ELSE -enh.h20 END WHEN 21 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h21 ELSE -enh.h21 END WHEN 22 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h22 ELSE -enh.h22 END WHEN 23 THEN CASE WHEN ts.add_subtract = 1 THEN enh.h23 ELSE -enh.h23 END END ) AS total_energy FROM ZTCA.EMS_monitoring_system_level1 level1 INNER JOIN ZTCA.EMS_monitoring_system_level0 level0 ON level0.id = level1.pid INNER JOIN ZTCA.EMS_monitoring_system_level2 level2 ON level1.id = level2.pid INNER JOIN ZTCA.t_report_statistics ts ON level2.id = ts.sys_id INNER JOIN ZTCA.EMS_meter_device_config mdc ON ts.device_id = mdc.id INNER JOIN ZTCA.energy_consumption_hour_10110 enh ON ts.device_id = enh.device_id CROSS JOIN ( SELECT 0 AS hour UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 ) hours WHERE level2.id IN ('1887325571596161025','1900061040134066178','1887325571495497730') AND enh.year = YEAR(NOW()) GROUP BY level0.simple_name, level1.simple_name, level2.simple_name, level2.id, mdc.name, enh.year, enh.month, enh.day, hours.hour ), aggregated_data AS ( SELECT level0_simple_name, level1_simple_name, level2_simple_name, level2_id, year, month, day, hour, SUM(total_energy) AS total_energy FROM base_data GROUP BY level0_simple_name, level1_simple_name, level2_simple_name, level2_id, year, month, day, hour ) -- 最终结果增加费用计算 SELECT a.*, CASE -- 电费计算:关联电价表 WHEN a.level2_id = '1900061040134066178' THEN a.total_energy * COALESCE(p.price, 0) -- 水费计算:固定单价3.3805 WHEN a.level2_id = '1887325571596161025' THEN a.total_energy * 3.3805 -- 蒸汽费计算:固定单价258.1651 WHEN a.level2_id = '1887325571495497730' THEN a.total_energy * 258.1651 ELSE 0 -- 其他表计费用为0 END AS cost FROM aggregated_data a -- 电价表关联(仅电表需要关联) LEFT JOIN ZTCA.EMS_electricity_price p ON a.year = p.year AND a.month = p.month AND a.day = p.day AND a.hour = p.hour AND a.level2_id = '1900061040134066178' -- 仅电表关联电价 修改以上SQL,仅按照level2_simple_name的维度汇总total_energy。最后只保留level2_simple_name,total_energy,折标煤,碳排放,cost五个字段
09-19
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值